topic Re: Bar chart to show cumulative total from current month to next N-month (using what-if parameter) in DAX Commands and Tips

Bar chart to show cumulative total from current month to next N-month (using what-if parameter)

New_hello188 — Wed, 27 Apr 2022 05:50:12 GMT

Hello !

Would like to show cumulative total based on current month till next N month(what-if parameter) .

Fact table :

I am only able to show sum of each month , but fail to show cumulative total

Measure A : Cumulative total ( Working correctly)

Cumulative_total = var max_date = MAX('Date'[Date]) var result = CALCULATE(CF_Table[CF_Sum], 'Date'[Date] <= max_date ) return result

Measure B : To show the sum of next N months data with what if parameter ( Working correctly)

CF_next_N = var slected = today() var result = CALCULATE(sum(CF_Table[CF_Amount_LCY]), DATESINPERIOD('Date'[Date],slected,+[Parameter Value],MONTH)) return result

Bar chart will show the total for next month that is based on my what-if parameter slicer.

- Facing error after edited the dax to show cumulative total instead of just total of each month.

- This is my attempt to show cumulative total for next N months.

Cumulative_total_N_try = var slected = today() var max_date = MAX('Date'[Date]) var result = CALCULATE(sum(CF_Table[CF_Amount_LCY]), DATESINPERIOD('Date'[Date],slected,+[Parameter Value],MONTH), filter(ALL(CF_Table), CF_Table[Cash_Flow_Date] <= max_date )) return result

My file is here.

Thanks in advance !

Re: Bar chart to show cumulative total from current month to next N-month (using what-if parameter)

SpartaBI — Tue, 26 Apr 2022 08:59:18 GMT

New_hello188 what do you mean by error? The measure is breaking or the result is wrong?
Anyway, without going deep on your model, just looking at the measure, try this:
Replace this part:
FILTER(CF_Table, CF_Table[Cash_Flow_Date] <= max_date)
with this:
CF_Table[Cash_Flow_Date] <= max_date
if not try this:
FILTER(ALL(CF_Table), CF_Table[Cash_Flow_Date] <= max_date).

Re: Bar chart to show cumulative total from current month to next N-month (using what-if parameter)

New_hello188 — Tue, 26 Apr 2022 09:33:30 GMT

Hi SpartaBI ,

Thanks for the response !

I tried and it is still showing total.

Just upload my file for your better reference.

Please get it from here : My file

Re: Bar chart to show cumulative total from current month to next N-month (using what-if parameter)

SpartaBI — Tue, 26 Apr 2022 10:19:23 GMT

Cool, could you please share an example of that are the numbers (the actual value) you want to get in the specific chart that is not working

Re: Bar chart to show cumulative total from current month to next N-month (using what-if parameter)

SpartaBI — Tue, 26 Apr 2022 10:22:03 GMT

Hey, I looked at the measure you tried, this is not what I meant, write this:

Cumulative_total_N_try = VAR slected = TODAY () VAR max_date = MAX ( 'Date'[Date] ) VAR result = CALCULATE ( CF_Table[CF_Sum], DATESINPERIOD ( 'Date'[Date], slected, + [Parameter Value], MONTH ), FILTER ( ALL ( CF_Table ), CF_Table[Cash_Flow_Date] <= max_date ) ) RETURN result

Re: Bar chart to show cumulative total from current month to next N-month (using what-if parameter)

New_hello188 — Wed, 27 Apr 2022 01:50:04 GMT

SpartaBI ,

Thanks for review !

I tried the measure as you suggested; the number looks like adding up and the interaction between bar chart and N-parameter slicer is weird ....

The cumulative total change according to the N-Parameter with still showing the same number of month on X-axis.

The final chart that I want to achieve is the bar chart that shows the cumulative total for next N-month on x-axis based on N-parameter.

- I can only able to shows the total but struggle with cumulative total.

- Below screenshot can be found in another page of my file.

Could you pls help to take a look ?

Re: Bar chart to show cumulative total from current month to next N-month (using what-if parameter)

Anonymous — Wed, 04 May 2022 01:47:26 GMT

Hi New_hello188,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, can you please share a pbix or some dummy data that keep raw data structure with expected results? They will help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng