Matrix Visual: Row subtotal blank/0 using DAX for custom periods

[leandroxps]

Hi all!

I'm developing some matrix visuals to analyze machine costs per month, but over the year.

But I'm trying to show row total per year and my formula is not showing.

I'm assuming the problem is in IF clause of actual DAX, because when I change BLANK() to 0 it shows 0,00 on Total Year column, but it breaks the logic showing more than the months selected in DAX (in this case, 12 months).

I have a slicer that select actual month to use this DAX to show 12 months (actual selected + 11 before).

My project doesn't use custom date slicer and my period is not last 12 months (it's a custom period based on slicer selection).

This is the only visual in the dashboard actually and the only slicer is the simple slicer of TBL_CALENDAR_1 table, that's connected to a TBL_CALENDAR_12 (1:* relationship), to make this logic works.

Is there anything that I can change in my DAX to make it works? Or is there a better way to do this kind of calculation?

Here's the example:

Here's my actual DAX formulas involved:

TotalCost12Month =
VAR MaxDate = MAX(TBL_CALENDAR_1[DATA])
VAR MaxDate12Month = EOMONTH(MaxDate, -13)
VAR Result =
IF (
HASONEVALUE(TBL_CALENDAR_12[DATA]) &&
MAX(TBL_CALENDAR_12[DATA]) <= MaxDate &&
MIN(TBL_CALENDAR_12[DATA]) > MaxDate12Month,
CALCULATE (
SUMX('_MEASURES_MCHN',[TotalCost]*1),
FILTER (
ALL (
TBL_CALENDAR_1[DATA]
),TBL_CALENDAR_1[DATA] = VALUES (TBL_CALENDAR_12[DATA])
)
),
BLANK()
)
RETURN Result

-------------------------------------------------

TotalCost = SUM(FACT_MACHINES[COST])

PBI Version:  2.124.1805.0 64-bit

[leandroxps · Accepted solution]

If somebody is getting the same problem, I found a solution to make it works correctly.

I considered that has no value of my CALENDAR TABLE in Subtotal Column, then I got the right result for my subtotal using a new measure.

For example:

TotalCost12Month =
VAR MaxDate = MAX(TBL_CALENDAR_1[DATA])
VAR MaxDate12Month = EOMONTH(MaxDate, -13)
VAR Result =
IF (
HASONEVALUE(TBL_CALENDAR_12[DATA]) &&
MAX(TBL_CALENDAR_12[DATA]) <= MaxDate &&
MIN(TBL_CALENDAR_12[DATA]) > MaxDate12Month,
CALCULATE (
SUMX('_MEASURES_MCHN',[TotalCost]*1),
FILTER (
ALL (
TBL_CALENDAR_1[DATA]
),TBL_CALENDAR_1[DATA] = VALUES (TBL_CALENDAR_12[DATA])
)
),
IF(NOT(HASONEVALUE(TBL_CALENDAR_12[DATA])),[TotalMonthsBefore],BLANK())
)
RETURN Result

In this scenario I have 2 totals:

1) TotalCost: Considering cost per month

2) TotalMonthsBefore: Considering the total for your custom period.

TotalMonthsBefore =

CALCULATE (

    SUM (FACT_MACHINES[COST]),

    FILTER (

        ALL (TBL_CALENDAR_1[DATE]),

        TBL_CALENDAR_1[DATE] <= MAX(TBL_CALENDAR_1[DATE]) &&

        TBL_CALENDAR_1[DATE] >= EOMONTH (MAX(TBL_CALENDAR_1[DATE]),-12 <here you can use how many months you want to consider for this measure>)

    )

)

[Greg_Deckler]

@leandroxps First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[leandroxps]

Hi Greg!

Thanks for the fast reply.
I was considering this solution, but my main problem is that I can't summarize my dates (or at least I dont know how to do it correctly), because my tables are not in relationship directly.

If I apply my simple DAX directly in a Matrix (TotalCost = SUM(FACT_MACHINES[COST])), or apply this logic you sent, it works fine for the actual period of my slicer.

But in this case where I have to filter a single date and bring months before, it's giving me some headache.

Here's a example:

My first matrix is with this solution you sent. It shows me all totals (line and column), but only for the actual month.

If I apply the formula for 12 months (my dates are dd/MM/yyyy pattern), my row totals disappear.
The second is using my DAX formula where I get months from an inactive relationship from my main date table.

Am I doing something wrong in my DAX formula?