MTD/QTD/YTD Slicer With YOY Comparison Table/Matrix- Yielding Blanks

[dhenders1833]

I am trying to construct a table or matrix that will show current year and prior year utilzing a MTD/QTD/YTD/Custom slicer.  The issue is that PY pulls in when custom is selected, however when MTD/QTD/YTD are selected it yields blanks.  I am hoping someone help me identify the issue as I am stumped!

Here my tables and relationship connections:

tbl_ReportData: fact table

tbl_Slicer: disconnected table with MTD, QTD, YTD, and Custom periods along with order

tbl_Date = CALENDAR(MIN(tbl_ReportData[Date]),MAX(tbl_ReportData[Date]))

tbl_DatePeriods =

UNION(

ADDCOLUMNS(

    DATESMTD(tbl_Date[Date]),

        "Type","MTD",

        "Order", 1

),

ADDCOLUMNS(

    DATESQTD(tbl_Date[Date]),

    "Type","QTD",

    "Order", 2

   

),

ADDCOLUMNS(

    DATESYTD(tbl_Date[Date]),

    "Type","YTD",

    "Order",3  

),

ADDCOLUMNS(

    CALENDAR(MIN(tbl_Date[Date]),MAX(tbl_Date[Date])),

    "Type","Custom",

    "Order",5    

    )

)

I made sure that "Both" is selected as the cross-filter direction between the tbl_Date and tbl_DatePeriods.  I also made sure tbl_Date is marked as a date table.

The measure I am using for PY is: 

PY Revenue = CALCULATE([Total Revenue],DATEADD(tbl_Date[Date],-1,YEAR)).

I have also tried PARALLEYEAR and SAMEPERIODASLASTYEAR and neither seem to work.

[DataNinja777]

Hi @dhenders1833 

There are many ways to achieve your required output and by using the disconnected table of the MTD, QTD, and YTD scenarios, you are on right track.  I tried to recreate your solution by copying and pasting the dax formula you've created, and I wasn't too sure what was the purpose of having tbl_DatePeriods in the data model.  

There seems to be simlpler ways to accomplish your task, and one of them is as follows:

1. Create a slicer table and set it as a disconnected table in your data model.  You can use database dax function but I just typed directly in grid in power query as it take as long as to write the formula.  

2) Create a selected measure formula linking the slicer table selection with the MTD, QTD and YTD measures.  I am assuming that you have already created MTD, QTD and YTD measures using the standard dax time intelligence functions prior to writing this selected measure formula.

3) Then, use another standard dax time intelligence function like selectedperiodlast year in conjunction with the [Selected Measure] formula to create the measure for the comparative figures for the selected scenarios.  

The resultant visualization is as follows where the [Selected Measure] and [Sameperiodlastyear for selected measure] is flexibly switched according to your choice in the slicer.  

I attach an example pbix file with the dummy data I created on the fly.  

Best regards,

[dhenders1833]

Thank you for the reply.  I tried following the steps that you provided, however the issue seems to still persist.   I am able to calculate PY when custom is selected, however whenever I select MTD/QTD/YTD, it returns a blank.  I believe it could have to do with the custom filter I have included, and/or with the possibility that there is not a prior year date for every current year date.  Any thoughts on a workaround?

When custom filter is selected:

When YTD is selected:

[DataNinja777]

Hi @dhenders1833 ,

I am assuming that you have created a calculated calendar table and created a relationship between the date field in your fact table (many side of the relationship) and calendar table date field (one side of the relationship) to take advantage of the dax time intelligence functions.  Please confirm this point, just in case. 

Best regards,

[dhenders1833 · Accepted solution]

I went back to review the example you provided have determined there are two issues: 1) there was a relationship issue between the tables.  Your example helped identify that, so that was a partial solution.  2) there is an issue when using the DATEADD function with a dataset that does not have consistent periods.  The set is comprised of sales by store, however there are new stores where there are not records from the prior year available.  For some reason, this causes the function to not work.  I will start a separate thread, as I am still perplexed by this piece.  I used PARALLELPERIOD instead and this in conjuction with the relationship changes seemed to work.  Thank you!

[dhenders1833]

Correct.  Please see below:

[xifeng_L]

What is the expression of the measure you are using in the matrix?

[dhenders1833]

The expression is: 

PY Revenue =

CALCULATE(

    [Selected Measure],

    SAMEPERIODLASTYEAR(tbl_Date[Date])

)

Where selected measure = 

Selected Measure = switch(min(tbl_Slicer[Order_By]),1,[MTD sales],2,[QTD sales],3,[YTD sales],4,[Total Revenue])