CODEWITHSUNDEEP
powerbipowerbi-desktop
user15213568
user15213568

Reputation: 1

PowerBI Previous Month Running Total for Filtered Categories

The questions relates to DAX/PowerBI. I've been trying to calculate a total for previous month for columns that contain only specific criteria (project name). My table looks more or less like this and is named Project Costs:

Project Name Date Cost in Month Month Year
X 01/01/2021 2.000,00 1 2021
Y 01/01/2021 1.500,00 1 2021
Z 01/01/2021 4.800,00 1 2021
X 01/02/2021 3.000,00 2 2021
Y 01/02/2021 3.500,00 2 2021
Z 01/02/2021 2.200,00 2 2021
X 01/03/2021 1.000,00 3 2021
Y 01/03/2021 6.000,00 3 2021
Z 01/03/2021 2.000,00 3 2021

I have a slicer in my report that allows me to select from all my projects. I want to be able to visualize different financial data relating only to the project currently selected with the slicer. I managed set up calculations for cost to date, labour to date etc., however, I am struggling to find a way to calculate a rolling previous month cost relating only to the selected project, that would be susceptible to the slicer in the visuals.

I've tried the following:

    Prev Month Cost Rolling = 
SUMX(RELATEDTABLE('Project Costs'), 'Project Costs'[Cost in Month],
DATEADD('Project Costs'[Date], -1, MONTH))

And it does not work as it says the max. argument count for SUMX is 2. I have tried solutions suggested here too, but they don't work either (I might be doing th wrong): Power BI Rolling Total Previous Month DAX

Expected result: Once the report is filtered to a project Z, in March I would expect to see in card visuals:

  1. Cost to date: 9.000,00
  2. Cost in previous month: 2,200.

When filtered for X in March it would want it to be as follows:

  1. Cost to date: 6.000,00
  2. Cost in previous month: 3.000,00

I want to calculate the entire previous month, not month to date.

I would appreciate any advise on how to tackle it! Thanks, J

Upvotes: 0

Views: 1159

Answers (1)

W.B.
W.B.

Reputation: 5525

Have a look at these measures:

Total Project Cost = CALCULATE([Cost], FILTER(ALL(Projects[Date]), Projects[Date] <= MAX(Projects[Date]))) - this shows all historical costs up to the selected date.

Cost = SUM(Projects[Cost in Month]) - can be used for costs in current month, for instance

Cost previous month = CALCULATE([Cost], PREVIOUSMONTH('Projects'[Date]))

enter image description here

Upvotes: 0

Related Questions