Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
eugeneniemand
Frequent Visitor

Month on Month at Day granularity repeating values

I'm doing month on month comparison on a day level. so column 1 is May and column 2 is April's figures however you can see on May 31st the value for April is being duplicated. I would have expected BLANK as there is no April 31st. Please see below

GP PM Test = 
     CALCULATE (
            [GP],
            DATEADD('Calendar'[Date],-1,MONTH)
        )

CrjYuIt

If I use previous month or parallel period when unfiltered or filtered for specific dates it returns the whole months total for everyday as seen here. When I select a full Month it still repeats for May 31st where I would have expected a blank

GP PM Test = 
     CALCULATE (
            [GP],
            PREVIOUSMONTH('Calendar'[Date])
        )

BgNdV3f

2 ACCEPTED SOLUTIONS
v-jiascu-msft
Employee
Employee

Hi @eugeneniemand,

 

Try this formula please.

GP PM Test =
CALCULATE (
    [GP],
    PARALLELPERIOD ( 'Calendar'[Datekey], -1, MONTH ),
    FILTER (
        ALL ( 'Calendar' ),
        DAY ( 'Calendar'[Datekey] ) = DAY ( MAX ( 'Calendar'[Datekey] ) )
    )
)

Month_on_Month_at_Day_granularity_repeating_values

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @eugeneniemand,

 

According to my understandings, the part is evaluated by the row context and the filter context. The filter context is from the visual. The blue part will iterate row by row of the 'Calendar', which means the row context. 

FILTER (
        ALL ( 'Calendar' ),
        DAY ( 'Calendar'[Datekey] ) = DAY ( MAX ( 'Calendar'[Datekey] ) )
    )

 For example, the day 5/30/2018. The MAX function always ignore the row context which is from "All('Calendar')". So the right side is 30. Then the blue part will be iterated row by row of the Calendar table. When the whole blue part is true, the rows will be chosen.

 

Reference: 

https://www.microsoftpressstore.com/articles/article.aspx?p=2449191 (There are always two contexts)

https://msdn.microsoft.com/en-us/query-bi/dax/filter-function-dax

 

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-jiascu-msft
Employee
Employee

Hi @eugeneniemand,

 

Try this formula please.

GP PM Test =
CALCULATE (
    [GP],
    PARALLELPERIOD ( 'Calendar'[Datekey], -1, MONTH ),
    FILTER (
        ALL ( 'Calendar' ),
        DAY ( 'Calendar'[Datekey] ) = DAY ( MAX ( 'Calendar'[Datekey] ) )
    )
)

Month_on_Month_at_Day_granularity_repeating_values

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-jiascu-msft, I should have mentioned I already have a measure that works, with similar logic. Yours is slightly simpler, but it breaks my head 🙂 do you mind explaining the filter part. I can see it looks at the whole calendar and then filters the calenedar where the days are equal but this is where I'm getting lost. 

DAY ( 'Calendar'[Datekey] ) = DAY ( MAX ( 'Calendar'[Datekey] ) )

Does the left side refere to each date in the calendar and then the right side is the MAX of the whole calendar? 

Hi @eugeneniemand,

 

According to my understandings, the part is evaluated by the row context and the filter context. The filter context is from the visual. The blue part will iterate row by row of the 'Calendar', which means the row context. 

FILTER (
        ALL ( 'Calendar' ),
        DAY ( 'Calendar'[Datekey] ) = DAY ( MAX ( 'Calendar'[Datekey] ) )
    )

 For example, the day 5/30/2018. The MAX function always ignore the row context which is from "All('Calendar')". So the right side is 30. Then the blue part will be iterated row by row of the Calendar table. When the whole blue part is true, the rows will be chosen.

 

Reference: 

https://www.microsoftpressstore.com/articles/article.aspx?p=2449191 (There are always two contexts)

https://msdn.microsoft.com/en-us/query-bi/dax/filter-function-dax

 

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.