topic Re: Running total by month in DAX Commands and Tips

Running total by month

Anonymous — Fri, 01 Jan 2021 16:18:58 GMT

Hi All,

I need to run a simply running total of a table which already have the revenue per months (not a per single day)

When my database is done by single day sales this formula works perfectly, but with a dataset done with months it gaves me back simply the sum value of each month without any running total calculation.

I don't know how to fix it, can anyone help?

Running Revenue = CALCULATE (sum('Sales Vs Budget'[Tot Value]),DATESYTD(months[Month]))

Re: Running total by month

MattAllington — Fri, 01 Jan 2021 21:02:44 GMT

DATESYTD is an inbuilt time intelligence function. You must follow the rules of a calendar table if you want it to work correctly in all circumstances. https://exceleratorbi.com.au/power-pivot-calendar-tables/

the parameter of DATESYTD must be a date. It is not clear if that is the case.
your visual must use columns from the calendar table. It is not clear if that is the case

Re: Running total by month

Anonymous — Sat, 02 Jan 2021 13:32:35 GMT

Hi Matt, thanks for the reply and the link, it was usefull and give me a part of my answer.

My issue is that I have some value listed per month and not by single day.

Note even that actually I use as filter table a table created by me where months are just format as month while they are written like the first day of each month (01.01.2020, 01.02.2020 and so on)

The point is that this kind of database doesn't have a single day history but just the month recap.

I need to run the running total even if I don't have the day granularity.

Is my problem now a bit clearer?

Thanks

Re: Running total by month

CNENFRNL — Sat, 02 Jan 2021 14:14:28 GMT

Hi, Anonymous , I attached a pbix file with calculation of monthly running total for your reference.

Re: Running total by month

Anonymous — Sat, 02 Jan 2021 14:57:44 GMT

Well thanks it work since the new field "mm-yyyy" is a stand alone field: when it automatically ask to create a hierarchy in it (which might be usefull as I would like to check the running even by quarters) this is no longer working.

Therefore I ask: is there a simply way to show the running total by months and then, eventually jump up to quarters view without creating new measure? if now which will be the measure for the quarters analysis

Thanks

Re: Running total by month

CNENFRNL — Sat, 02 Jan 2021 16:03:08 GMT

Anonymous , no such a simple measure like CALCULATE([Total Qty], FILTER(Year or Quarter or Month or Week or Day)) to automatically apply to different granularity of time.

Re: Running total by month

Anonymous — Sun, 03 Jan 2021 09:23:40 GMT

Well i've taken your example: your new column "YYYY-MM" is formatted like a text in the file. If I transform it into "Date" it automatically creates the date hierarchy which doesnt work with the running total.

If I haven't done anything wrong you can try with your own example 😕

Re: Running total by month

amitchandak — Mon, 04 Jan 2021 05:50:16 GMT

Anonymous , In what format you have a month. From there you can do create a date and then you formula will work

Example: Jun-2020

date = "01-" & [Month] // please change the datatype to date

Example : 202006

date =date(left([month],4), right([month],2),1)

The join with date table use the same formula that you have like

Running Revenue = CALCULATE (sum('Sales Vs Budget'[Tot Value]),DATESYTD(Date[Date]))

refer first 5 mins - https://www.youtube.com/watch?v=yPQ9UV37LOU