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

[efowler]

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

[parry2k]

@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.

[parry2k]

@efowler here is the link to the video and yes channel is still PeryTUS youtube.com/perytus

https://youtu.be/6lGQKACZbNk

[parry2k]

@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]

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]

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,

[efowler]

@Thejeswar - Getting closer!   What I am trying to show is comparison year over year similar to the below.  

[Thejeswar · Accepted solution]

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,

[efowler]

Nailed it!  Thank you very much! 

[parry2k]

@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]

Thank you..  Is your YouTube channel still PeryTUS? 

https://www.youtube.com/channel/UCOYOaONaLGxtHQZxKosUegA

[Thejeswar]

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]

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]

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]

@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]

No, the desired output is shown below..     New .pbix file (corrected file) is in the link below..

https://app.box.com/s/02tyg0aqr9etf8e94mvls5e4hpkekax9