DAX Formula not working as Expected

[Anonymous]

Hello, i'm having issues with a Measure that doesnt' work like I hoped.

This is the stripped data-model

The idea is that the users select a Period from DIM_Date_Periods, that has a bi-directional relation to DIM_DATES.

Periods could be 2017, 2016 or 2016+ (That's 2016&2017).

We are Measuring a BackLog of OTRS Tickets. When a Ticket is created on a date it Creates a NET_INCREASE of 1, when closed on same or another day it created a NET_INCREASE of -1.

The Simple DAX formula for the backlog is SUM(NET_INCREASE).

But, even on the first day of 2017 (if period 2017 is selected) there was already a backlog running ... So to avoid the selection on the dates is created the DAX

BackLog = CALCULATE(SUM('FACT_TICKET'[TICKET_NET_INCREASE]), FILTER(ALL(DIM_DATE), DIM_DATE[DATEKEY] <= MAX(DIM_DATE[DateKey])))

But, the FILTER(ALL(DIM_DATE) only takes in account the facts from dates 2017*, so my historical backlog isn't summed.

I hoped to see always 10288 for 2017-01 (Wich is field BACKLOG_PERIOD from DIM_DATES).

Where is my error .... Kind regards

[v-huizhn-msft]

Hi @Anonymous,

You can create a new date table, but don't need to create a relationship with your factual table.

Select the NewTable[Year] as slicer, then create a measure to get the value selected in slicer.

select-year=CALCULATE(MAX(NewTable[Year]),ALLSELECTED(NewTable))

Then create a measure to calculate the BlackLog using the formua, and check if it works fine.

BackLog = CALCULATE(SUM('FACT_TICKET'[TICKET_NET_INCREASE]), FILTER(ALL(DIM_DATE), DIM_DATE[DATEKEY] <= NewTable[select-year])))

Best Regards,
Angelia

[v-huizhn-msft]

Hi @Anonymous,

Based on my understanding, you used the [DateKey] in your DAX, so the BackLog is running total based on the [DateKey] instead of [Year]. Please create a calculated column to get year using the formula: Year=Year[DateKey].

Then replace [DateKey] to [Year] as the following formula, please check if it works fine.

BackLog = CALCULATE(SUM('FACT_TICKET'[TICKET_NET_INCREASE]), FILTER(ALL(DIM_DATE), DIM_DATE[Year] <= MAX(DIM_DATE[Year])))

Don't hesitate to ask if you have any issue.

Best Regards,
Angelia

[Anonymous]

Problem is that the Backlog has to be calculated Day by Day to see the evolution. When using the Year in the Formula it isn't calculated correctly by day.

But based on your idea I used the ADATE field, with is the real date field there where datekey is a whole number based on yyyymmdd. But even by using the ADATE field the CALCULATE(SUL ... doesn't take in account the records before the first date chosen by the slicer ...

I thought that the ALL overrules the filters caused by a slicer.

[v-huizhn-msft]

Hi @Anonymous,

Have you resolved your issue? If you have, welcome to share your solution and mark the right reply as answer.

Best Regards,
Angelia