Cumulative (Running Total) total year over year comparison using year not in the DATE Table

Hello, I cam struggling to figure out how to model this and write the DAX.. Can anyone help?

My desired output is a line graph showing year over year cumulative total for "Net Warranty". I am able to calculate the running cummulative total but I need it broken up by the 'Warranty'[date recieved] Year in which each year for date recieved is starts over at zero and the running total accumluates for that "date recieved" year.

Line Graph X Axis = Calendar Month from date recieved column

Line Graph Y Axix = Running Total of "net warranty"

Legend = Year from the "Date recieved" column in the warranty table

PBIX file is availalbe for download --> https://app.box.com/s/02tyg0aqr9etf8e94mvls5e4hpkekax9

Need Help

Thejeswar

4 years ago

Hi efowler ,

You got to build a summary table using the above created measure. The DAX is as below

newtable = ADDCOLUMNS(SUMMARIZE(Warranty,Warranty[Date Recvd], "@MonthNO", MONTH(Warranty[Date Recvd]), "@MONTH", SWITCH(MONTH(Warranty[Date Recvd]),1,"Jan",2,"Feb",3, "Mar", 4, "Apr", 5, "May", 6, "Jun", 7, "Jul", 8, "Aug", 9, "Sep", 10, "Oct", 11, "Nov", 12, "Dec"), "@YEAR", YEAR(Warranty[Date Recvd])), "running_net_warranty", 'Measure'[running_net_warranty])

The Table will look like as below

Build a graph using this

In the graph sort the Month column with Month No

Hope this solves what you wnat!! If this answers, Mark it as solution!! Appreciate a Kudo!!

Regards,

15 Replies

parry2k
Super User
4 years ago
Thejeswar This solution works but it will not work as soon as you filter anything in the date dimension because it is not using the Date dimension.

Although the solution which I'm talking about is making sure that we are taking advantage of Date/Calendar dimension so that everything works as expected even if we use anything from the calendar dimension as a slicer, maybe that is not a need for this question but still it is always good to have a scalable solution. Just my 2 cents.
- efowler
  Helper II
  4 years ago
  Thank you both for your support... The measure works in the table but as parry2k mentions it does not work whith added filter/date diminsion. Any suggestions?
  - Thejeswar
    Super User
    4 years ago
    Hi efowler ,
    I modified the DAX a bit and I see it is now giving the right results
    
    1. Created a new column based on Date received to give me Year received in the warranty table
    Year received = YEAR(Warranty[Date Recvd])
    2. Create a new running total measure as like shown below
    running_net_warranty = IF(HASONEFILTER(Warranty[Date Recvd]), CALCULATE([Net Warranty], Warranty[Date Recvd] <= MAX(Warranty[Date Recvd]), GROUPBY(Warranty,Warranty[Year received])))
    
    The Below is the table and chart
    Having only the running net warranty measure to clearly show it
    Guess this is what you expected.
    
    Regards,
parry2k
Super User
4 years ago
efowler is this what you are looking for?

✨ Follow us on LinkedIn and to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
- efowler
  Helper II
  4 years ago
  No, the desired output is shown below.. New .pbix file (corrected file) is in the link below..
  
  https://app.box.com/s/02tyg0aqr9etf8e94mvls5e4hpkekax9
Thejeswar
Super User
4 years ago
Hi efowler ,
If you are looking for running total, I suspect that won't give the right values.
Your recovery measure is giving $81 through out for all date received, although there is no match for that particuar market. Here's the ss

Do check it out!!
- efowler
  Helper II
  4 years ago
  Whoops, sorry about that.. I had a couple errors in my sample data and relationships... I fixed that issue now and have the new .pbix in the link below. Also have an example of the desired output in the excel file.
  
  https://app.box.com/s/02tyg0aqr9etf8e94mvls5e4hpkekax9
  - Thejeswar
    Super User
    4 years ago
    Hi efowler ,
    You can use the below DAX to get the Net Waranty running total based on the Data received column in warranty
    running_net_warranty = CALCULATE([Net Warranty], DATESYTD(Warranty[Date Recvd]))
    
    I saw the expected output in excel that you shared. This one matches with it. After the measure is created, set the decimal places to 0
    
    Below is the screenshot
    
    Hope this helps!! Mark it as solution, if this is the excepted!! Appreciate a Kudo!!
parry2k
Super User
4 years ago
efowler Got it. It is an interesting question and I'm going to do a video on this and post it on my YT channel. Stay tuned. Do subscribe to stay up to date, once the video is ready I will surely post the link here as well.

BTW, in the excel sheet for 2022, you entered the wrong data in the table from which you created the line chart.

Cheers,
- efowler
  Helper II
  4 years ago
  Thank you.. Is your YouTube channel still PeryTUS?
  
  https://www.youtube.com/channel/UCOYOaONaLGxtHQZxKosUegA
parry2k
Super User
4 years ago
efowler here is the link to the video and yes channel is still PeryTUS youtube.com/perytus

https://youtu.be/6lGQKACZbNk
parry2k
Super User
4 years ago
efowler ouch, not something I would do, but if it works for you, good. This is not a scalable and the right approach. I will leave it here.