Align Days of week from different years

[dmoggie]

Hi All,

I am trying to compare the same month from this year and last. The issue I'm encountering is the data/days of the week do not align. I do not require weekends. Infact I have no weekend data.  

.

I am using a date table. My DAX

Total Flow 2019 = CALCULATE(SUM('2019v2020_Norm'[Total Flow 2020]),SAMEPERIODLASTYEAR('Calendar Table'[Date]))

Anyway of amended SAMPERIODLASTYEAR to acheieve desired outcome?? or is there another option?

Im pretty new to PowerBI so learning as I go.

[v-xicai]

Hi @dmoggie ,

You may create measures like DAX below.

Total Flow 2019 =
CALCULATE (
    SUM ( '2019v2020_Norm'[Total Flow 2020] ),
    FILTER (
        ALLSELECTED ( '2019v2020_Norm' ),
        YEAR ( '2019v2020_Norm'[Date] ) = YEAR ( MAX ( '2019v2020_Norm'[Date] ) )
            && MONTH ( '2019v2020_Norm'[Date] )
                = MONTH ( MAX ( '2019v2020_Norm'[Date] ) ) - 1
    )
)


Total Flow 2020 =
CALCULATE (
    SUM ( '2019v2020_Norm'[Total Flow 2020] ),
    FILTER (
        ALLSELECTED ( '2019v2020_Norm' ),
        YEAR ( '2019v2020_Norm'[Date] ) = YEAR ( MAX ( '2019v2020_Norm'[Date] ) )
            && MONTH ( '2019v2020_Norm'[Date] ) = MONTH ( MAX ( '2019v2020_Norm'[Date] ) )
    )
)

Best Regards,

Amy 

Community Support Team _ Amy

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

[dmoggie]

Hi Amy,

Thank you for takin time out to help. I am still unable to get this working correctly using these suggested measures. Days are correct, but the 2019 data just will not populate, I must be doing something wrong!

I have now added a week number field to my date calendar. This sort of works, as I can filter by this, but its still not really how I need it. 

KR  

[amitchandak]

@dmoggie , just make sure the calendar is marked as date table.

you can also try trailing year measure

example

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

[dmoggie]

Hi,

Thank you for the quick response. I can confirm the date table is marked as such. i have also use th elast year measure, but I get the same result.

KR

[dmoggie]

Morning all,

Really stuck with this. I cannot seem to get last years data to not populate Sat & Sun.