Summarize values by Category less or equal to Slicer value

[AhmadJamil]

I have 3 slicers on my following data (Category1, Category2, Period) and I need a Card showing the Total of (Value) based on slicers, which is easy. The part where I need the help is to show a bar chart with Period on X-Axis and Values on Y-Axis. This Chart should respond to Category1 and Category 2 normally, nevertheless, all Periods lower or equal to Period Slicer should be shown on the X-Axis. e.g. if Period 2023 (2) is selected in the slicer it should show two bars providing total of (Value) for Period 2023 (1) and Period 2023 (2).

Category 1	Category 2	Period ID	Period	Value
P1	M1	1	2023 (1)	20
P1	M2	1	2023 (1)	30
P1	M3	1	2023 (1)	50
P1	M1	1	2023 (1)	41
P2	M2	1	2023 (1)	52
P2	M4	1	2023 (1)	36
P2	M1	1	2023 (1)	60
P2	M1	1	2023 (1)	47
P2	M3	1	2023 (1)	59
P1	M1	2	2023 (2)	83
P1	M2	2	2023 (2)	70
P1	M3	2	2023 (2)	82
P1	M1	2	2023 (2)	106
P2	M2	2	2023 (2)	93
P2	M4	2	2023 (2)	105
P2	M1	2	2023 (2)	129
P2	M1	2	2023 (2)	116
P2	M3	2	2023 (2)	128
P1	M1	3	2023 (3)	152
P1	M2	3	2023 (3)	139
P1	M3	3	2023 (3)	151
P1	M1	3	2023 (3)	175
P2	M2	3	2023 (3)	162
P2	M4	3	2023 (3)	174
P2	M1	3	2023 (3)	198
P2	M1	3	2023 (3)	185
P2	M3	3	2023 (3)	197

So, assuming I have selected Period "2023 (2)", it should show me following chart, which should be responsive to Category1 and Category 2 as well:

Please let me know if I need to make a measure or a calculated table (which I doubt because Calculated table will not respond to my slicer).
Thanking you in anticipation.

Ahmad

[gmsamborn]

Hi @AhmadJamil , @DataInsights 

I curious why you didn't set your slicer to Period ID, in Slicer Settings set to Less than or equal to, and rename the slicer from Period ID to Period.

Also, I didn't see anything in the requirements about displaying cumulative totals instead to period totals.

Sorry if I'm way off base here.

Mine - no measures.pbix

[DataInsights]

@gmsamborn,

Using a Before slicer is a good approach, and one that I typically use. It gives you more flexibility with date calculations, and it's simpler for users to set one date and have the entire report calculate per that date. The Periods table would actually be replaced by a robust date table in a production model.

Check the requirement "all periods lower or equal to period slicer". This could be achieved with a YTD calculation, which is likely what would happen in a production model so years aren't combined.

Thanks for your comments!

[gmsamborn]

Hi @DataInsights 

I'm guessing where that is a part of "all Periods lower or equal to Period Slicer should be shown on the X-Axis."

Also, "show a bar chart with Period on X-Axis and Values on Y-Axis."

Regardless, if a date table isn't being used already , it should be.  Then functions like DATESYTD and TOTALYTD would be usable.

[DataInsights · Accepted solution]

@AhmadJamil,

Try this solution. Create a calculated table Periods:

Periods = SUMMARIZE ( FactTable, FactTable[Period], FactTable[Period ID] )

This table has no relationship with FactTable. Create measure:

Cumulative Total = 
VAR vPeriodSelection =
    SELECTEDVALUE ( Periods[Period ID] )
VAR vResult =
    IF (
        MAX ( FactTable[Period ID] ) <= vPeriodSelection,
        CALCULATE (
            SUM ( FactTable[Value] ),
            FILTER (
                ALLSELECTED ( FactTable ),
                FactTable[Period ID] <= MAX ( FactTable[Period ID] )
            )
        )
    )
RETURN
    vResult

Create slicer using Periods[Period]. Create visual using FactTable[Period] and the measure above.

This technique is explained in the article below.

https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/ 

[AhmadJamil]

Hi DataInsights, 
Thank you so very much, yes I was looking for somehting like this. I have apply it to a large complex solution where the same Filter is also being used to filter other visuals that are based on fact table. Do you have any suggestion to have a single filter not based on Periods table.

Regards,
Ahmad

[DataInsights]

@AhmadJamil,

Do you have a mockup of what you are trying to achieve?