Running total by month

[GianlucaM]

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

[amitchandak]

@GianlucaM , 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

[CNENFRNL]

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

[GianlucaM]

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

[CNENFRNL]

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

[GianlucaM]

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 😕

[MattAllington]

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

[GianlucaM]

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