Forum Discussion
Post ProdigyCumulative total over 2 years
Hi,
I have quick question.
Is it possible to create measure which will sum cumulatevily values over 2 years?
I mean it must be something like YTD, but for 2 years, not only one year.
My current measure looks like this:
CALCULATE(
SUM(
Invent_Trans (Inventory Value);
DATESYTD(DimDates(Date))
My problem arise when 2020 start, so my cumulative Revenues are approximately 0, but I need to sum them with previous year cumulatevily.
Hi pawelj795 ,
First, one step in the above formula was complicated by me. I have modified it. Please check:
YTD Revenues 5 = VAR LastYearFirstDate = IF ( SELECTEDVALUE ( 'Calendar'[Year] ) = BLANK (), DATE ( YEAR ( LASTDATE ( Invent_Trans[Date] ) ) - 1, 1, 1 ), DATE ( SELECTEDVALUE ( 'Calendar'[Year] ) - 1, 1, 1 ) -------------changed ) VAR CurrentDate = IF ( SELECTEDVALUE ( 'Calendar'[Year] ) = BLANK (), MAX ( DimDates[Date] ), CALCULATE ( MAX ( DimDates[Date] ), FILTER ( DimDates, DimDates[Year] = SELECTEDVALUE ( 'Calendar'[Year] ) ) ) ) RETURN CALCULATE ( SUM ( Invent_Trans[Inventory Value] ), FILTER ( ALLSELECTED ( DimDates ), DimDates[Date] >= LastYearFirstDate && DimDates[Date] <= CurrentDate ) )Then, you can create your YearWeek column like so:
YearWeek = CONCATENATE ( Invent_Trans[Year], CONCATENATE ( " ", FORMAT ( Invent_Trans[WeekNum], "00" ) ) )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
13 Replies
- amitchandak
Super User
Try Like
Sales 2 Years = IF( NOT ISEMPTY( Sales ), CALCULATE( [Sales], DATESINPERIOD('DimDates'[Date],ENDOFMONTH(Sales[Sales Date]),-2,YEAR)) ) )Appreciate 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 
pawelj795Icey
amitchandak
I was just trying this solution, but it doesn't seem working.
Why you using EndOfMonth function? What is the purpose?- Icey
Community Support
Hi pawelj795 ,
I create a simple example. What about this?
YTD Revenues 3 = VAR LastYearFirstDate = DATE ( YEAR ( LASTDATE ( Invent_Trans[Date] ) ) - 1, 1, 1 ) VAR CurrentDate = MAX ( DimDates[Date] ) RETURN CALCULATE ( SUM ( Invent_Trans[Inventory Value] ), FILTER ( ALLSELECTED ( DimDates ), DimDates[Date] >= LastYearFirstDate && DimDates[Date] <= CurrentDate ) )For details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
i have a very similar problem.
I have a dimensional table, containing the number of Stores, I want to have the cumulative sum per year of the number of active stores.See example:
therefore the number of active stores in 2012 is given by the sum of the stores created in 2011 and 2012.
The number of stores active in 2013 will be given by the sum of the stores active in 2011 and 2012.
Can you help me to solve this problem?
Thank you
