Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

DAX Formula not working as Expected

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-directi...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      v-huizhn-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      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