topic Re: Time Intelligence: Previous Month Value is Incorrect in DAX Commands and Tips

Time Intelligence: Previous Month Value is Incorrect

VikrantC — Tue, 22 Mar 2022 13:46:07 GMT

Hi,

I have 3 month daily data for 4 players (X1, X2, X3 and Y1). The data is arranged in 3 columns (Date, Player, Score). All the players have data for each date (no gaps). There is no score on weekend (no rows for weekend). I am able to get Score for end of the month (specific date). However, I am unable to get Data for the previous month (end of the month). I would like to see scores for X1, X2, X3 and Y1 on 2/28, 1/31, 12/31 in one column and calculate % change from the previous month.

The second problem I am having is I want to Filter X1 and X2 (on date) and use variable to calculate Sum of X1, or (X1*Y1^2). Please see the sample data in the file below.

PBI_Data (file://DESKTOP-47L4URJ/PBI_Data)PBI_Data (file://DESKTOP-47L4URJ/PBI_Data)

I am not sure how to attach PBI file. Pease let me know if the folder cannot be open (Dropbox).

Re: Time Intelligence: Previous Month Value is Incorrect

amitchandak — Wed, 23 Mar 2022 16:39:03 GMT

VikrantC , Based on what I got last date of the month

example

MTD Sales = CALCULATE(lastnonblankvalues('Date'[Date],SUM(Sales[Sales Amount])),DATESMTD('Date'[Date]))

Re: Time Intelligence: Previous Month Value is Incorrect

VikrantC — Wed, 23 Mar 2022 17:53:38 GMT

Hi Amit,

Thank you very much. I used a similar formula. However, I am still stuck with "The second problem I am having is I want to Filter X1 and X2 (on date) and use variable to calculate Sum of X1, or (X1*Y1^2). Please see the sample data in the file below". The X1 and X2 have same dates and values.

If I have two variables (X1 and X2) with dates and values (3 columns total). How do I manipulate the formula. For example, I want to SUM (X1^2*X2). Basically, for each specific dates I want to calculate X1square and multiply it with X2, Ideally, I want this to be a variable so that I can use it as part of other DAX formula.

Re: Time Intelligence: Previous Month Value is Incorrect

VikrantC — Thu, 24 Mar 2022 10:50:51 GMT

Hi Amit,

Your formula did not work. I am more interested in the second problem. Any idea?. Thanks.

Re: Time Intelligence: Previous Month Value is Incorrect

v-kkf-msft — Fri, 25 Mar 2022 05:16:18 GMT

Hi VikrantC ,

I do not have access to the link you shared, please use SharePoint Online or other tools to share your file.

Based on my understanding, I created the following example data.

1. Then create the measure to calculate the month end score value and compare it to the previous month end.

MonthEndScore = CALCULATE ( SUM ( 'Table'[Score] ), FILTER ( 'Table', 'Table'[Date] = EOMONTH ( 'Table'[Date], 0 ) ) )

Score% = IF ( [MonthEndScore] <> BLANK (), DIVIDE ( [MonthEndScore], CALCULATE ( [MonthEndScore], FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = EOMONTH ( MAX ( 'Table'[Date] ), -1 ) && 'Table'[Player] = MAX ( 'Table'[Player] ) ) ), 1 ) - 1 )

2. Create the measure to calculate X1*Y1^2.

X1*Y1^2 = CALCULATE ( SUM ( 'Table'[Score] ), 'Table'[Player] = "X1" ) * CALCULATE ( POWER ( SUM ( 'Table'[Score] ), 2 ), 'Table'[Player] = "Y1" )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Time Intelligence: Previous Month Value is Incorrect

VikrantC — Fri, 25 Mar 2022 15:39:31 GMT

Thank you very much. This is working. I have one question. Regarding X1*Y12. How do I use the Measure Score%.

Eventually, I was to use a Variable for (X1*Y1^2) using the Score% (% change over month for X1 and Y1).

Again, thank you very much.

Best Regards,

Re: Time Intelligence: Previous Month Value is Incorrect

VikrantC — Fri, 25 Mar 2022 16:20:08 GMT

One more question to add to the earlier question. How about I used % of change per month (instead of each date). Would it be easier to summarize X1, Y1 in columns?

Re: Time Intelligence: Previous Month Value is Incorrect

VikrantC — Fri, 25 Mar 2022 16:20:56 GMT

Thanks Amit. For some reason it did not work. I did get it. Again, thank you very much for your help.

Re: Time Intelligence: Previous Month Value is Incorrect

v-kkf-msft — Wed, 30 Mar 2022 03:21:19 GMT

Hi VikrantC ,

Sorry for the late reply. Please try the following formula:

Measure = CALCULATE ( [Score%], 'Table'[Player] = "X1" ) * CALCULATE ( POWER ( [Score%], 2 ), 'Table'[Player] = "Y1" )

Best Regards,
Winniz

Re: Time Intelligence: Previous Month Value is Incorrect

VikrantC — Wed, 30 Mar 2022 12:31:07 GMT

Looks good. I am going to try it out tonight. Have a great day.