Forum Discussion
Helper IIIHow can i get cumulative total for below data using dax
Dear Sir
I have created cumulation dax,
| Actual Cumulative COl = |
| CALCULATE(Sum('Cumulative Test'[Actual]),FILTER(ALLEXCEPT('Cumulative Test','Cumulative Test'[Location]),'Cumulative Test'[MY] <= Earlier('Cumulative Test'[MY] ))) |
Every time i use this dax, It cumulates from month 1,
but i want cumulation to start from month 4 Q1,
- Hi GAURAV7781 ,First, do you have a real date column in your table? If there is no difference between your fact data and the data you provide in data type, please try the following steps:1. Create an index column in "edit queries" as a filter condition for cumulative value2. DAXActual Cumulative =
IF (
NOT ( ISBLANK ( 'Cumulative Test'[Actual] ) ),
CALCULATE (
SUM ( 'Cumulative Test'[Actual] ),
FILTER (
ALLEXCEPT ( 'Cumulative Test', 'Cumulative Test'[Location] ),
'Cumulative Test'[Index] <= EARLIER ( 'Cumulative Test'[Index] )
)
)
)You can also refer to the pbixBest Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
If you want cumulative only for qtr or year, you can use datesytd and datesqtd too.
Can you share how final outcome will look like?
GAURAV7781I have attached outcome in above link
- amitchandak
Make sure you have date calendar and then try
CALCULATE(Sum('Cumulative Test'[Actual]),datesytd(date[date],"3/31"))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.
Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functionsAppreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
V-lianl-msftCommunity Support
Hi GAURAV7781 ,First, do you have a real date column in your table? If there is no difference between your fact data and the data you provide in data type, please try the following steps:1. Create an index column in "edit queries" as a filter condition for cumulative value2. DAXActual Cumulative =
IF (
NOT ( ISBLANK ( 'Cumulative Test'[Actual] ) ),
CALCULATE (
SUM ( 'Cumulative Test'[Actual] ),
FILTER (
ALLEXCEPT ( 'Cumulative Test', 'Cumulative Test'[Location] ),
'Cumulative Test'[Index] <= EARLIER ( 'Cumulative Test'[Index] )
)
)
)You can also refer to the pbixBest Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
