Dynamic (Years, quarters, months) with dynamic YoY, QoQ, MoM

[Anonymous]

Hi,

I have a Matrix with the following aspect:

With the upper filter I can chose between Year, Quarter or Month and everything works fine:

The problem is that I would like to show the headers with the following format:

Year -> 2017, 2018, 2019....

Quarter -> 2017-Q1, 2017-Q2...

Month -> 2017-01, 2017-02...

For this purpose I change the Value column from my auxiliar Dim_Table:

Here the Matrix table works fine with the "Total Tickets" and "Percentage" measures:

Total_Tickets_PreviousYear =
CALCULATE(
DISTINCTCOUNT('Volume Creados'[TaskID]);PREVIOUSYEAR(dim_Date_freq[Value]))

[Anonymous]

Hi @Anonymous ,

You can create one measure as below:

Measure =
SWITCH (
    MAX ( 'Format'[Attribute] ),
    "Year", CONCATENATE ( YEAR ( MAX ( 'Format'[Value] ) ), "" ),
    "Quarter", CONCATENATE (
        YEAR ( MAX ( 'Format'[Value] ) ),
        CONCATENATE ( "-Q", QUARTER ( MAX ( 'Format'[Value] ) ) )
    ),
    "Month", CONCATENATE (
        YEAR ( MAX ( 'Format'[Value] ) ),
        CONCATENATE ( "-", MONTH ( MAX ( 'Format'[Value] ) ) )
    )
)

Best Regards

Rena

[Anonymous]

Hi @Anonymous 

I can't put a Measure in a Column of a Matrix table and if I try with a new calculate column the "Previous Year" measure disapears:

[Anonymous]

Hi @Anonymous ，

The newly added measurement value or calculated column is recognized as a text value , it is no longer a date value . Hence the Time-intelligence function PREVIOUSYEAR will not work . If you want to calculate Total_Tickets_PreviousYear , you can update your previous formula as below :

Total_Tickets_PreviousYear =
CALCULATE (
    DISTINCTCOUNT ( 'Volume Creados'[TaskID] );
    PREVIOUSYEAR ( dim_Date_freq[Date] )
)

Best Regards

Rena

[Anonymous]

Hi @Anonymous ,

Your furmula is the initial measure that I show in the post and it doesn't work.

I thing that I need a diferent measure to calculate the PreviousYear metric.

[Anonymous]

Hi @Anonymous ，

Could you please provide your pbix file if it is convenient? Thank you.

Best Regards

Rena