topic Re: Need help with Previous Month Calculation in DAX Commands and Tips

Need help with Previous Month Calculation

Prateek_121 — Wed, 24 Jul 2024 15:32:41 GMT

Hi Team,

I am creating a DAX measure to display the sum of previous month value. I have 1 more column in my fact table which I need to use in the same report and that column stores the value of flag yes or no which changes per month ( or maybe after two months). If I add that column in my report, my dax date time function ( any DateADD, parallelperiod ) adds that flag in the context and now if for example I have flag as Y in Dec 23 and in Jan 24 flag is N in the data then I get two rows for Jan 24 in the report one with flag Y and other with flag N and previous month value is displayed with flag Y where as row with flag value N shows blank. This is because now based on grouping which now has become reporting month and the flag after adding that column to the report. We need the column in our report and if I use allseelected() in my formula I am not do filters on other dim with multiselect since I get subtotals then. Is there a way with which I can avoid the column being used in the context for calculating previous month. Please note that are 10 such flag columns for now so adding each column in the formula or adding each dimension in the formula is not a valid solution. Also if there is a way I can use two different formulas that is also fine.

Re: Need help with Previous Month Calculation

Prateek_121 — Wed, 24 Jul 2024 15:34:07 GMT

the formual for new logic measure is

var _abc = calculate([original exposure], allselected(table[originalexposure]), parallelperiod(date, -1 month)

return if([original exposure] = blank(), blank(), [original exposure] - _abc)

And I have used this formula also but it does subtotals if I add other dimensions and do a multi select

var _abc = calculate([original exposure], allselected(table), parallelperiod(date, -1 month)

return if([original exposure] = blank(), blank(), [original exposure] - _abc)

Unfortunately I cannot share screenshot because of sensitive data

Re: Need help with Previous Month Calculation

Anonymous — Thu, 25 Jul 2024 03:56:43 GMT

Hi, Prateek_121

Can you simulate some dummy data to tell your question? Ideally, it will also provide the output you expect.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Need help with Previous Month Calculation

Prateek_121 — Thu, 25 Jul 2024 09:14:02 GMT

https://drive.google.com/file/d/1cCHUarTpIhDZDpUm0DZ_K2qY3zusu_GQ/view?usp=sharing -- link for the dummy dataset. Chart 1 and 2 are expected outputs but you can see both scenarios are not working with any measure . Chart 5 shows what is happening with date time intelligence function when I have added the flag column in the chart.

Re: Need help with Previous Month Calculation

Prateek_121 — Thu, 25 Jul 2024 09:14:28 GMT

added dataset in my reply post

Re: Need help with Previous Month Calculation

Prateek_121 — Thu, 25 Jul 2024 09:17:12 GMT

I have 10-12 dimensions like this with high number of distinct rows so using keepfilters() on each dimension is not a feasible solution for me