CODEWITHSUNDEEP
powerbidax
Winston
Winston

Reputation: 3

PowerBI Period Slicers (QTD/YTD) not slicing

I am trying to create period slicers for QTD/YTD/Last 12 Month (LTM) to create an interactive report dashboard.

I've set up all the Measures I think are required based on "Actual" data.

AC = SWITCH([PeriodSelect], 1, [AC Select], 2, [AC QTD], 3, [AC YTD], 4, [AC LTM])

AC LTM = CALCULATE([AC Select], DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]),-12, MONTH))

AC QTD = CALCULATE([AC Select], DATESQTD('Calendar'[Date]))

AC Select = CALCULATE([AC Sum], Scenario[ScenarioName] = "Actual")

AC Sum = SUM(Actual[Value])

However, the slicer only shows the Values (AC). The [AC Select] is working fine, and I can slice it by Month and Year. However, AC QTD/YTD/LTM remains blank.

There are no error messages and I do not know what is wrong. I know the question is a bit vague but would appreciate if anyone can look it over https://www.dropbox.com/s/bavlq5h360ax27y/TestReport.pbix?dl=0

Upvotes: 0

Views: 593

Answers (1)

RADO
RADO

Reputation: 8148

As mentioned, date slicers should be based on the calendar table, not actual dates. Once you fix it, your formulas work correctly.

It might look like periods are not slicing, but that's not correct - they are. The reason you see the same numbers is because in your data sample, for March 2022, the results are the same for QTD, YTD and LTM periods.

This is how your data looks:

enter image description here

Notice that for QTD and YTD the numbers are the same because in March, you are in the first quarter, and the date interval is the same (Jan 1 2022 - March 31 2022). For later quarters, they will be different.

For LTM, the date interval is April 1, 2021 - March 31 2022, but in 2021 you only have data for January and February, so it's not included. As a result, the AC sum is the same.

To see that it's working, select January 2022 instead:

enter image description here

and the AC selector works fine too.

Upvotes: 0

Related Questions