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
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?
Solved! Go to 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.
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.
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
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @jtemes ,
Don't know how you have your model setup and the calculations but I have made the following setup:
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCheck out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
106 | |
91 | |
67 |
User | Count |
---|---|
162 | |
133 | |
132 | |
93 | |
91 |