Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Thanks in advance !
@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).
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
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
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 ?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |