Need urgent help with a visual that has past 3 months of data and few static columns.

[ankiegraze]

Hello all,

Basically I am trying to create a visual which displays data for the past 3 months corresponding to the month selected in the slicer. Note that I have my months in columns.  Along side that, I want few columns to remain static. For example, when I am selecting lets say december 2024 in the slicer. I should be seeing the data for october, november and december 2024. Further more I want some columns, lets say "baseline numerator", "baseline denominator" and "Baseline % Compliant" to remain static. I dont want them to come seperately for every month. Since their values remains the same for the entire measurement period (July to June), they should appear only once.  The following visual only talks about one month at a time. I want to change it as described above.

[Anonymous · Accepted solution]

Hi, @ankiegraze 
Thanks for reaching out to the Microsoft fabric community forum.

1.First, you need to create a calculated table as the new date slicer. Please note that there should be no relationship between the two tables.

slicer = VALUES('Table'[Date])

2.Secondly, assuming your measure is Measure:

Measure = SUM('Table'[values])

If you need a dynamic measure, please refer to the following formula:

dynamic = 
VAR selectd=MAX('slicer'[Date])
 RETURN  CALCULATE([Measure], FILTER(ALLSELECTED('Table'),'Table'[Date]>=EDATE(selectd,-3)&&'Table'[Date]<=selectd))

If you need a static measure, please refer to the following formula:

static = CALCULATE([Measure],ALL('Table'[Date]))

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

If I'm wrong, can you share sample data and sample output in tabular form? Or a sample with sensitive data removed. We can better understand the problem and help you. Especially your computational logic, and your data structures.

Best Regards,

Leroy Lu

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