Forum Discussion

Squizzato's avatar
Squizzato
Regular Visitor
3 years ago

DATEADD Fail with QUARTER???

Hi all,

I'm tring to use DATEADD to create a Measure that compare data with previous QUARTER data. Everything is ok when I use date field from my fact table. When I use calendar table joined with fact table on the same date field, I obtain something impredictable.

PBI create an extra row in a date where there is no data (30/12/2022).

Measure formulas are very simple:

Working one

MvValue Prev Quarter (Original Date) = CALCULATE(SUM(ValuesTable[MyValue]), DATEADD(ValuesTable[Original Date], -1, QUARTER))
 
Not working
MvValue Prev Quarter (Calendar Date) = CALCULATE(SUM(ValuesTable[MyValue]), DATEADD('Calendar'[Date], -1, QUARTER))
 
Relationships

I marked Calendar Table as Date table.

I don't understand why PBI create row on 30/12/2022.

I would like to use calendar table for all my formulas, not every date in every fact table.

Please help!

Thanks

 

Andrea

Need Help

2 Replies

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

    Squizzato add following measure and see what dates you are getting, you are just moving 3 months back, so for Dec 30th and Dec31st of 2022, you are moving one quarter and it is giving you 'Sep 30th, 2022 which has value of 9 and that's why you are getting the value 9 for both the dates.

     

    Dt = 
VAR __date = DATEADD ( 'Calendar'[Date], -1, QUARTER ) 
RETURN MINX ( __date, [Date] ) & " - " & MAXX ( __date, [Date] )

     

    Check the time intelligence video series on my channel to understand how these function works https://youtube.com/playlist?list=PLiYSIjh4cEx3GXH6WnT8yIQCPGC5jmoPL

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

     

  • Squizzato's avatar
    Squizzato
    Regular Visitor
    3 years ago

    Hi parry,

    thanks for your answer. I'll watch your interesting video. Anyway is there a DAX formula that point at the last month day of previous 3 months? I tried with EOMONTH but 

    MvValue Prev Quarter (EOMONTH) = CALCULATE(SUM(ValuesTable[MyValue]), EOMONTH('Calendar'[Date], -3)) formula repeat only total value for every row.