Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jtemes
Helper I
Helper I

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

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?

1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

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]))

vhenrykmstf_0-1642756692223.png

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.

View solution in original post

6 REPLIES 6
v-henryk-mstf
Community Support
Community Support

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]))

vhenrykmstf_0-1642756692223.png

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
Helper I
Helper I

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
Community Support
Community Support

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
Helper I
Helper I

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

 

 

 

 

 

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" )
)

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix
Super User
Super User

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:

MFelix_0-1642515845268.png

 

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:

MFelix_1-1642516029286.png

 

MFelix_2-1642516043303.png

 

Check PBIX file attach.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.