cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

dynamic running sum month number changing

Hi Folks,

 

I guess my previous message did not go through 😉

Well, I want to have a running sum on a ratio, and compare year over year.

The tricky part is the users wants to change the start of the year and to be able to choose the month starting the year. So, my running sum could start in May 2019 and thus I want to compare the sum by month from May 2019 to April 2020 and the sume by month from May 2020 to April 2021.

May becomes then month 1...

 

How can I achieve that ?

I tried many things, but I can't pass the value of a slicer to correctly recalculate the month numer...it is even weirder as I can pass the value as a measure, but not as a column...really, I am stuck...

 

If anyone has an idea..

PY

8 REPLIES 8
v-lionel-msft
Community Support
Community Support

Hi @Anonymous ,

 

Please refer to my .pbix file.

v-lionel-msft_0-1600153386448.png

 

Best regards,
Lionel Chen

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

Anonymous
Not applicable

Hi @v-lionel-msft , @Greg_Deckler , @amitchandak ,

 

Thanks for the follow up, this is really nice to see the support!!

 

@v-lionel-msft , yo uare getting close, but what I would like as the first column is the Month name, and then for the following columns the running sum starting at the month for the last 12 month, and the running sum starting at the same month name, but for the 12 months prior to the last 12 month. 

So :

- Col 1 = Month Name of the date in my fact table

- Col 2= running sum starting on the month name selected in a seletor, and very line is the running sum of the previous up to month 12 after the first one

- Col 3 = same as Col 2, but with an offset of 12 months...

 

I created a dummy file..but so far no luck...

https://drive.google.com/file/d/1iWBbw9wf1mcH0NPQeCh-Rd8TCWHJsMbe/view?usp=sharing

 

PY

amitchandak
Super User
Super User

@Anonymous , I think this would be more ideal case for rolling 12 months vs rolling 12 months.

 

Use a date table for that.

//last 12
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH)) //or
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,MONTH))  //or
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date],-12,MONTH))   


//12 before 12
Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-12,month)),-12,MONTH))
Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd(Sales[Sales Date],-12,month)),-12,MONTH))
Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd('Date'[Date],-12,month)),-12,MONTH))

 

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-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
Anonymous
Not applicable

Hi @amitchandak 

 

Thanks for the reply! I feel less alone 😉

But this is not a rolling 12 months, but more a YTD...

I want in the end to compare how year over year I am progressing. Something like: 

Month NoYear1CumYear 1Year2CumYear2% YoY
110105050(50-10)/10*100
220301060(60-30)/30*100
3307045115(115-70)/70*100
44011030145(145-110)/110*100

 

The month number is actually defined by the month the user selected...

 

So, we are not really doing a rolling 12, but a ytd, where the year does not neessarily start in Jan...

I will try your option to see if I can play withe dates, but the month number change is really painful

 

@Anonymous If this isn't solved can you post sample data (text) and expected output from that sample data.

 

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler 

 

Thanks for jumping in. I really liked your comments in the links you gave me, especially about the "unintuitiveness" (I decided to invent workds today 😉 ) of the time "intelligent" functions...

 

By the way, your post was helpful, but so far the issue I face is that I can not, based on a selection, change the month number in the data. And as the rolling sum function is basically taking ascending or descending function as a basis, if I can't change the month number I am screwed...

This is really disappointing...

 

Will continue digging, but I really doubt I will succeed... Will keep you posted

 

PY

@Anonymous Can you post PBIX or sample data as text?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Anonymous , You can YTD like. Please understand the end date year is end date year to find start date.

Of you choose may 2002. it will give data from Jan 2020 to May 2020

if you Choose Jun 2020 it will give data from Jan 2020 to Jun 2020

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

The end date "12/31" can be changed. But as far I know it does not a measure, to make it dynamic. That is why I suggested rolling

 

Check out datesytd

 

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.