Change column header dates by month or quarter based on a slicer value

[jtemes]

Hi all

I have a table where the column header are showing quarterly data and total for the year, and a slicer to select one particular date.

Based on this date, I want that the quarter related to the date of the slicer opens up by month. For example

- Slicer Date: 28/02/2022 would show data by months in Q1 and by quarters for the rest of the year. Then columns of the table should be: January, February, March, Q1, Q2, Q3, Q4, Year 

- Slicer Date: 30/04/2022 would show data by months in Q2 and by quarters for the rest of the year. Then columns of the table should be: Q1, April, May, June Q2, Q3, Q4, Year

- Slicer Date: 31/07/2022 would show data by months in Q3 and by quarters for the rest of the year. Then columns of the table should be: Q1, Q2, July, August, September, Q3, Q4, Year

- Slicer Date: 31/12/2022 would show data by months in Q4 and by quarters for the rest of the year. Then columns of the table should be: Q1, Q2, Q3, October, November, December, Q4, Year

Can you please help me to do this?

[v-henryk-mstf · Accepted solution]

Hi @jtemes ,

According to your description, refer to the following formula:

Expanded Total = 
IF(HASONEVALUE(Periods[Value]),IF (
    SELECTEDVALUE ( Periods[Quarter] )
        = SELECTEDVALUE ( 'calendar slicer'[Quarter] ),
    [Total by month/quarter],
    CALCULATE ( [Total by month/quarter], Periods[Type] = "Quarter" )
),sum('Table'[Value]))

If the problem is still not resolved, please point it out. Looking forward to your reply.

Best Regards,
Henry

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

[jtemes]

Hi @MFelix  and @v-henryk-mstf 

It looks good. Would it be possible to show also the Total value. Currently is blank

Thank you very much for your help

[v-henryk-mstf]

Hi @jtemes ,

I agree with the scenario offered by @MFelix . If the test results are incorrect, please point out the specific problem and provide a screenshot of the desired results and I will get back to you as soon as possible.

Looking forward to your feedback.

Best Regards,
Henry

[jtemes]

Hi @MFelix 

Thanks for your reply. I came up with something similar to your solution, however I am missing the total per quarter for the current quarter. 

In your example, when you select 01-03-2022, you get Jan, Feb, Mar, Q2, Q3, Q4 and Total. I would need a column for Q1 too.

Thanks

[MFelix]

Hi @jtemes ,

Just redo the measure to:

Expanded Total =
IF (
    SELECTEDVALUE ( Periods[Quarter] )
        = SELECTEDVALUE ( 'calendar slicer'[Quarter] ),
    [Total by month/quarter],
    CALCULATE ( [Total by month/quarter], Periods[Type] = "Quarter" )
)

[MFelix]

Hi  @jtemes ,

Don't know how you have your model setup and the calculations but I have made the following setup:

• Fact table
• Calendar table (Related to the fact)
• Calendar table for slicer (unrelated)
• Periods for the columns

Periods table looks like this:

Then I created the following two measures:

Total by month/quarter =
IF (
    LEFT ( SELECTEDVALUE ( 'Periods'[Value] ), 1 ) = "Q",
    CALCULATE (
        SUM ( 'Table'[Value] ),
        'calendar'[Quarter] = MAX ( 'Periods'[Value] )
    ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        'calendar'[Month] IN VALUES ( 'Periods'[Value] )
    )
)

Expanded Total =
IF (
    SELECTEDVALUE ( Periods[Quarter] )
        = SELECTEDVALUE ( 'calendar slicer'[Quarter] ),
    CALCULATE ( [Total by month/quarter], Periods[Type] = "Month" ),
    CALCULATE ( [Total by month/quarter], Periods[Type] = "Quarter" )
)

This may need some changes but is working has needed:

Check PBIX file attach.