Power BI Exchange

Hello,

Attached is the PBI and excel for your quick reference.

PBI report has 2 table i.e. Dates Table and Report Table1.

I'm trying to achieve last 12 months division calculation, which is not giving me the correct values.

In excel, column I (highlighted) are the values, which I'm trying to achieve in PBI report.

For which I created a measure to get the count of Completed from status column and then created a Monthly Target column in DimDates table, which will show target value for each month.

Once we get correct values as in column I, then I need to calculate cumulative (Column J)

Please suggest.

------------------------------
Prem Guru
------------------------------

Hi Prem,

In your Calculateasure, you are using a COUNTA function that will return the number of rows in the date Monthly target column.



------------------------------
Vincent L.
Chartered accountant - Expert-comptable
------------------------------

Original Message:
Sent: Nov 26, 2020 09:39 AM
From: Prem Guru
Subject: DAX for Division not working

Hello,

Attached is the PBI and excel for your quick reference.

PBI report has 2 table i.e. Dates Table and Report Table1.

I'm trying to achieve last 12 months division calculation, which is not giving me the correct values.

In excel, column I (highlighted) are the values, which I'm trying to achieve in PBI report.

For which I created a measure to get the count of Completed from status column and then created a Monthly Target column in DimDates table, which will show target value for each month.

Once we get correct values as in column I, then I need to calculate cumulative (Column J)

Please suggest.

------------------------------
Prem Guru
------------------------------

Hi Vincent,

I replaced the measure with the one provided by you and seem to be working, but the cumulative is not giving the correct values.

Cumulative of Average = 
CALCULATE(
	[Calculate],
	FILTER(
		DATESYTD('DimDates'[Month End]),
		ISONORAFTER('DimDates'[Month End], MAX('DimDates'[Month End]), DESC)
	)
)​

Thanks!

------------------------------
Prem Guru
------------------------------

Original Message:
Sent: Nov 26, 2020 04:08 PM
From: Vincent L.
Subject: DAX for Division not working

Hi Prem,

In your Calculateasure, you are using a COUNTA function that will return the number of rows in the date Monthly target column.

You should replace it with SELECTEDVALUE or MAX to a single value of the monthly target :

Calculate = DIVIDE([Completed], SELECTEDVALUE('DimDates'[Monthly Target]))

------------------------------
Vincent L.
Chartered accountant - Expert-comptable

Original Message:
Sent: Nov 26, 2020 09:39 AM
From: Prem Guru
Subject: DAX for Division not working

Hello,

Attached is the PBI and excel for your quick reference.

PBI report has 2 table i.e. Dates Table and Report Table1.

I'm trying to achieve last 12 months division calculation, which is not giving me the correct values.

In excel, column I (highlighted) are the values, which I'm trying to achieve in PBI report.

For which I created a measure to get the count of Completed from status column and then created a Monthly Target column in DimDates table, which will show target value for each month.

Once we get correct values as in column I, then I need to calculate cumulative (Column J)

Please suggest.

------------------------------
Prem Guru
------------------------------

Updated PBI attached

------------------------------
Prem Guru
------------------------------

Original Message:
Sent: Nov 27, 2020 01:57 AM
From: Prem Guru
Subject: DAX for Division not working

Hi Vincent,

I replaced the measure with the one provided by you and seem to be working, but the cumulative is not giving the correct values.

Cumulative of Average = 
CALCULATE(
	[Calculate],
	FILTER(
		DATESYTD('DimDates'[Month End]),
		ISONORAFTER('DimDates'[Month End], MAX('DimDates'[Month End]), DESC)
	)
)​

Thanks!

------------------------------
Prem Guru

Original Message:
Sent: Nov 26, 2020 04:08 PM
From: Vincent L.
Subject: DAX for Division not working

Hi Prem,

In your Calculateasure, you are using a COUNTA function that will return the number of rows in the date Monthly target column.

You should replace it with SELECTEDVALUE or MAX to a single value of the monthly target :

Calculate = DIVIDE([Completed], SELECTEDVALUE('DimDates'[Monthly Target]))

------------------------------
Vincent L.
Chartered accountant - Expert-comptable

Original Message:
Sent: Nov 26, 2020 09:39 AM
From: Prem Guru
Subject: DAX for Division not working

Hello,

Attached is the PBI and excel for your quick reference.

PBI report has 2 table i.e. Dates Table and Report Table1.

I'm trying to achieve last 12 months division calculation, which is not giving me the correct values.

In excel, column I (highlighted) are the values, which I'm trying to achieve in PBI report.

For which I created a measure to get the count of Completed from status column and then created a Monthly Target column in DimDates table, which will show target value for each month.

Once we get correct values as in column I, then I need to calculate cumulative (Column J)

Please suggest.

------------------------------
Prem Guru
------------------------------

Prem,

Can you try using this measure :

Cumulative of Average = VAR referencedate=MAX(DimDates[Date])
RETURN CALCULATE([Calculate],
    FILTER(ALL(DimDates[Date]),
    DimDates[Date]<=referencedate &&
    DimDates[Date]>DATE(YEAR(referencedate)-1,12,31)
    )
)​

I have a few rounding differences with your Excel file.

------------------------------
Vincent L.
Chartered accountant - Expert-comptable
------------------------------

Original Message:
Sent: Nov 27, 2020 03:06 AM
From: Prem Guru
Subject: DAX for Division not working

Updated PBI attached

------------------------------
Prem Guru

Original Message:
Sent: Nov 27, 2020 01:57 AM
From: Prem Guru
Subject: DAX for Division not working

Hi Vincent,

I replaced the measure with the one provided by you and seem to be working, but the cumulative is not giving the correct values.

Cumulative of Average = 
CALCULATE(
	[Calculate],
	FILTER(
		DATESYTD('DimDates'[Month End]),
		ISONORAFTER('DimDates'[Month End], MAX('DimDates'[Month End]), DESC)
	)
)​

Thanks!

------------------------------
Prem Guru

Original Message:
Sent: Nov 26, 2020 04:08 PM
From: Vincent L.
Subject: DAX for Division not working

Hi Prem,

In your Calculateasure, you are using a COUNTA function that will return the number of rows in the date Monthly target column.

You should replace it with SELECTEDVALUE or MAX to a single value of the monthly target :

Calculate = DIVIDE([Completed], SELECTEDVALUE('DimDates'[Monthly Target]))

------------------------------
Vincent L.
Chartered accountant - Expert-comptable

Original Message:
Sent: Nov 26, 2020 09:39 AM
From: Prem Guru
Subject: DAX for Division not working

Hello,

Attached is the PBI and excel for your quick reference.

PBI report has 2 table i.e. Dates Table and Report Table1.

I'm trying to achieve last 12 months division calculation, which is not giving me the correct values.

In excel, column I (highlighted) are the values, which I'm trying to achieve in PBI report.

For which I created a measure to get the count of Completed from status column and then created a Monthly Target column in DimDates table, which will show target value for each month.

Once we get correct values as in column I, then I need to calculate cumulative (Column J)

Please suggest.

------------------------------
Prem Guru
------------------------------

Thanks Vincent, but I'm not getting the correct values for calculating the cumulative.

I've attached excel file for your reference (highlighted table).

Lastly when we have the correct cumulative values as per excel, then the YTD column is to be calculated by month no.

Appreciate if you could move me to the right direction.

------------------------------
Prem Guru
------------------------------

Original Message:
Sent: Nov 27, 2020 12:11 PM
From: Vincent L.
Subject: DAX for Division not working

Prem,

Can you try using this measure :

Cumulative of Average = VAR referencedate=MAX(DimDates[Date])RETURN CALCULATE([Calculate],    FILTER(ALL(DimDates[Date]),    DimDates[Date]<=referencedate &&    DimDates[Date]>DATE(YEAR(referencedate)-1,12,31)    ))​

I have a few rounding differences with your Excel file.

------------------------------
Vincent L.
Chartered accountant - Expert-comptable

Original Message:
Sent: Nov 27, 2020 03:06 AM
From: Prem Guru
Subject: DAX for Division not working

Updated PBI attached

------------------------------
Prem Guru

Original Message:
Sent: Nov 27, 2020 01:57 AM
From: Prem Guru
Subject: DAX for Division not working

Hi Vincent,

I replaced the measure with the one provided by you and seem to be working, but the cumulative is not giving the correct values.

Cumulative of Average = 
CALCULATE(
	[Calculate],
	FILTER(
		DATESYTD('DimDates'[Month End]),
		ISONORAFTER('DimDates'[Month End], MAX('DimDates'[Month End]), DESC)
	)
)​

Thanks!

------------------------------
Prem Guru

Original Message:
Sent: Nov 26, 2020 04:08 PM
From: Vincent L.
Subject: DAX for Division not working

Hi Prem,

In your Calculateasure, you are using a COUNTA function that will return the number of rows in the date Monthly target column.

You should replace it with SELECTEDVALUE or MAX to a single value of the monthly target :

Calculate = DIVIDE([Completed], SELECTEDVALUE('DimDates'[Monthly Target]))

------------------------------
Vincent L.
Chartered accountant - Expert-comptable

Original Message:
Sent: Nov 26, 2020 09:39 AM
From: Prem Guru
Subject: DAX for Division not working

Hello,

Attached is the PBI and excel for your quick reference.

PBI report has 2 table i.e. Dates Table and Report Table1.

I'm trying to achieve last 12 months division calculation, which is not giving me the correct values.

In excel, column I (highlighted) are the values, which I'm trying to achieve in PBI report.

For which I created a measure to get the count of Completed from status column and then created a Monthly Target column in DimDates table, which will show target value for each month.

Once we get correct values as in column I, then I need to calculate cumulative (Column J)

Please suggest.

------------------------------
Prem Guru
------------------------------