Comparative Figures by Period

[Anonymous]

I have a question regarding comparative figures. Essentially I am building highlevel Card visuals that show Revenue numbers for example.

Currently these Card visuals show Total Revenue based on three filters - 1. Financial Year, 2. Month and 3. MTD, QTD or YTD, depending on what the user wants to see. Also note that the data set contains 10 years worth of data.

What I am looking to do, is show a comparative number next to the existing card visual, based on the same three filters as mentioned above. Based on the Financial Year and Month selected, I want the comparatives to show as follows based on the third filter. If:

YTD, then show comparative month a year ago (e.g. Mar2018 YTD vs March2017 YTD)

QTD, then show comparative with prior quarter (e.g. Mar2018 QTD vs Dec2017 QTD)

MTD, then show comparative with prior month (e.g Mar2018 MTD vs Feb2018 MTD)

I have tried doing this (just for a single comparative for now) with the following measure, however I get a big round X in my visual.

YTDComparative = if ( isfiltered('Period Selector'[Period])="YTD",

calculate([TotalRevenueVariable], parallelperiod('Calendar'[Date],-1,year))

,[TotalRevenueVariable])

TotalRevenueVariable is used to flip data between YTD, QTD and MTD based on the following measure:

TotalRevenueVariable = IF ( HASONEVALUE ('Period Selector'[Period]),
SWITCH ( VALUES ('Period Selector'[Period]),
"YTD", [TotalRevenueYTD],
"MTD", [TotalRevenueMTD],
"QTD", [TotalRevenueQTD]
)
,[TotalRevenueYTD])

Any thoughts on how I can create the dynamic comparative figures?

[v-shex-msft · Accepted solution]

Hi @Anonymous,

Switch function cannot work properly on summarized records which contains multiple case items.

If you mean use switch function to analysis and split records to YTD, QTD, MTD from summary records, it is impossible. 

ISFILTERED function only return bool value, I think you have misunderstood for this fucntion. 

You can also try to use below formula if it works for your requirement.

TotalRevenueVariable =
IF (
    HASONEVALUE ( 'Period Selector'[Period] ),
    SWITCH (
        SELECTEDVALUE ( 'Period Selector'[Period] ),
        "YTD", [TotalRevenueYTD],
        "MTD", [TotalRevenueMTD],
        "QTD", [TotalRevenueQTD]
    ),
    [TotalRevenueYTD]
)

YTDComparative =
IF (
    SELECTEDVALUE ( 'Period Selector'[Period] ) = "YTD",
    CALCULATE (
        [TotalRevenueVariable],
        PARALLELPERIOD ( 'Calendar'[Date], -1, YEAR )
    ),
    [TotalRevenueVariable]
)

Regards,

Xiaoxin Sheng