Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Help with DAX in DATEADD

Hi,

i have the data in the following format

 

datemm - yyyyloan idnetwork nameoffice nameamount
8/9/202308 - 20231n1o1200
8/9/202308 - 20233n1o1100
8/8/202308 - 20232n2o2500

 

 

MTD =
IF(ISNUMBER(SUM(Table[amount])),SUM(Table[amount]),0)

tot amt Last year =
CALCULATE(
[MTD],
DATEADD(
'Table'[MM-YYYY],
-1,
YEAR
)
)


Difference of year =
[MTD] - [tot amt Last year]

Percent.of year =
IF([tot amt Last year] = BLANK(),BLANK(),
DIVIDE([Difference of year],[tot amt Last year]))  

 

 

This works perfectly fine until any filter is applied.

Once any of the filter is applied, the visual is collapsed and following errors is thrown.

 

 

 

I tried with sameperiodlastyear as well. But couldnt resolve this issue.

 

Please help

Ashish_Mathur 

 

 

Thanks,

Dharani

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    No i didn't have a calendar table in the model

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Make a date table with this code:
      CalendarAuto()

       

       

      Go to "Model" view (on left hand side of desktop) and create a relationship between "Date" from your main table and date from the calendar table. Let me know if this fixes your measures. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi mate,

    Do you have a Date/Calendar table in your data model? If so how is your relationship set up?

     

    Thanks

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I cannot understand your question.  For the sample data that you have shared, explain the question and show the expected result.  Be clear in your explanation.