Compare DateTime Periods and not Date Periods

[themistoklis]

Hello All,

I have a report (attached on this message) with two tables.

The one on the left compares current year/quarter/month/week against the same period in the previous year.

The table on the right does a YoY, QoQ, MoM, WoW comparison.

The formulas work correctly but when I select DayNames and Hours from the slicers the formulas do not work (expecially the ones that look on previous year/quarter/month data).

That's because the functions (e.g. PREVIOUSMONTH, SAMEPERIODLASTYEAR) only work on dates and not on datetimes.

I would like to have tha ability to select Monday and Tusday and compare the metrics for these days only (or hours).

Any hep will be much appreciated

[MFelix · Accepted solution]

Hi @themistoklis,

Since you have the filtering at a weekday level and hour level you need to change the calculation mode:

Views Same Period Last Year V2 = 
VAR Total_Per_Month =
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Calendar Date Hour' ),
            'Calendar Date Hour'[Year-Month],
            "@ViewsCurrentValue", [Views Current],
            "@YearMonth+1",
                LEFT ( 'Calendar Date Hour'[Year-Month], 4 ) + 1
                    & RIGHT ( 'Calendar Date Hour'[Year-Month], 3 )
        ),
        [@YearMonth+1] IN VALUES ( 'Calendar Date Hour'[Year-Month] )
    )
RETURN
    SUMX ( Total_Per_Month, [@ViewsCurrentValue] )

What I did was to create a sumarizzation table based on your lowest granularity in this case the Year-Month, then I added on the temporary table the next year month. This allows me to have a table with two columns for the same value this year and next year then I only filter the table to give me the total for those lines

Result below has you can see V2 is different from V1

[themistoklis]

@MFelix 

It works great! Thanks a lot

[MFelix]

Hi @themistoklis ,

You need to change the way you get the calculation done in this case filtering the values you need for the hour and days.

Add a column for the previous year date:

Same Day Last Year = DATEADD('Calendar Date Hour'[Date],-1,year)

now redo your measure to:

Views Same Period Last Year = 
CALCULATE (
    [Views Current],
    FILTER (
        ALL ( 'Calendar Date Hour' ),
        CONTAINS (
            VALUES ( 'Calendar Date Hour'[Same Day Last Year] ),
            'Calendar Date Hour'[Same Day Last Year], 'Calendar Date Hour'[Date]
        )
            && CONTAINS (
                VALUES ( 'Calendar Date Hour'[Hour] ),
                'Calendar Date Hour'[Hour], 'Calendar Date Hour'[Hour]
            )
    )
)

Please check attach file if the calculations are correct when filtering. If there is need to have additional changes to the measure please tell me.