Calculating Running Total using Quick measure and am getting incorrect values

[Isaiahj09]

Hi All,
I am calculating the running total using quick measures which is easy enough but I am getting incorrect values

The Base Value is a measure called Total Revenue. The Field is Date which contains the dates at the End of Month only
The Total Revenue measure consists of two measures added together

Please see the screenshot below:

I have also tried using DATESINPERIOD and still it returns the same incorrect values:

CALCULATE([Total Revenue],DATESINPERIOD('Alteryx TB'[Date],MAX('Alteryx TB'[Date]),-12,MONTH))

Is there anything I may be doing wrong?

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

Try this approach

1. Create a Calendar Table with calculated column for Year, Month name and Month number
2. Sort the Month name by the month number
3. Create a relationship (Many to One and Single) from the Date column of the Fact table to the Date column of the Calendar Table
4. To your visual, drag Year and Month from the Calendar Table
5. Write this measure

Measure = CALCULATE([Total Revenue],datesbetween(calendar[date],edate(min(calendar[date]),-11),max(calendar[date])))

Hope this helps.

[Anonymous · Accepted solution]

Thanks for the reply from Ashish_Mathur and vicky_  , please allow me to provide another insight:

Hi, @Isaiahj09 

Can you tell me if your problem is solved? If yes, please accept their reply as solution.

Your dax is currently working fine in my test environment, so I'm guessing that the reason why your run is not what you expect may be due to your "Total Revenue" measure, and here are my measurements:

1.First I have created the following table and the column names and data are the data you have given:

 2. Below are the measure I've created for your needs:

Total Revenue = SUM('Alteryx TB'[1])

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

 
Of course, you can also try updating your desktop version to see if that solves the problem:
Download Microsoft Power BI Desktop from Official Microsoft Download Center
 

Best Regards,

Leroy Lu

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

[Isaiahj09]

Thank you !
It seems it was indeed an issue with the dates in my original table.

[Ashish_Mathur · Accepted solution]

Hi,

Try this approach

1. Create a Calendar Table with calculated column for Year, Month name and Month number
2. Sort the Month name by the month number
3. Create a relationship (Many to One and Single) from the Date column of the Fact table to the Date column of the Calendar Table
4. To your visual, drag Year and Month from the Calendar Table
5. Write this measure

Measure = CALCULATE([Total Revenue],datesbetween(calendar[date],edate(min(calendar[date]),-11),max(calendar[date])))

Hope this helps.

[Isaiahj09]

Thank you! The numbers are fixed now with this method
Marked as solved !

[vicky_]

I suspect there's something funky happening with your calculate and dates, but it isn't really possible to debug with the given information.  However, if you've got a more recent version of PowerBI, you can try using the running sum in Visual Calculations which will handle those types of calculations for you- https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview#usin...