Power BI - CY, PY Calculation - DAX

[bkar81_2]

Dear All,

I have a request in PBI dax. I tried many things, but I am not sure what I am doing wrong. Can you please let me know the solution?

Request: I have a date column (year). I pulled that in Filters on All Pages filter. Based on that, the data is getting filteried only for those years...

When I calculate Current Year and Previous year, I need to get the max of the year selected in the Page Level filter and keep that in CY. PY = CY-1

Say for example, I have years 2020 till 2024. If I choose 2020 and 2021 (in page level filter), then CY should be 2021 and PY should be 2020, even though I have data more than 2021.

I tried all these

Cy = Max(Date)

Cy = Calculate(Max(Date), ALL(Date))

Cy = Calculate(Max(Date), RemoveFilters(Date))

etc... But nothing is working.. If I check CY & PY in a card visual, it is showing correctly, but when I drag and drop the same columns in Table/Pivot visual, it is showing all the year's data, as CY is dynamically calculated on each rows.

The year filter should be only in Filters on All Pages and not inside any page as a Slicer or so.

As of now, I am creating individual reports for each year (2020-2021, 2021-2022, 2022-2023) like that. I want to have a single report which dynamically takes CY as the max year selected and PY as CY-1 (based on the user's selection).

I hope I have explained it clearly. If it is not clear, please let me know. Your help would be highly appreciable.

Thank you so much in advance.

[Trevor_G · Accepted solution]

Yes, ALLSELCTED() allows items in the filter context (your page filters) pass through to the calculation. 

More information (with examples) on ALLSELECTED() can be found here: 
https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

ALL(), REMOVEFILTERS(), and ALLSELECTED() are key modifiers for the filter context in BI, any time spent understanding them is well worth while. 

[bkar81_2]

Thank you for the link.

I created a measure and used ALLSELECTED() and it partially fixed the issue.

CY = CALCULATE(MAX('Table1'[Date]), ALLSELECTED())

I have created other measures which used the above measure, but in few cases I used AVGX/SUMX.

CY Sales = CALCULATE(SUM('Table1'[Sales]), FILTER('Table1', 'Table1'[Date] = [CY]))

In those measures, the value was not populating correctly and as mentioned in the link, it gave weird results.

So I deleted the main measure that has ALLSELECTED() and created a variable under other measures that needs that ALLSELECTED() and everything worked fine.

CY Sales = 
VAR cy = CALCULATE(MAX('Table1'[Date]), ALLSELECTED())
RETURN
CALCULATE(SUM('Table1'[Sales]), FILTER('Table1', 'Table1'[Date] = cy))

The above is just a sample code and not actual code.

So I understood that ALLSELECTED() will not go well with SUMX/AVGX (X functions).

[bkar81_2]

I tried

Cy = Calculate(Max(Date), ALLSELECTED(Date))

Cy = Calculate(Max(Date), ALLSELECTED(Table))
Both didn't work...
Finally I tried
Cy = Calculate(Max(Date), ALLSELECTED()) - nothing passed inside ALLSELECTED and it gave the expected result.

Is this correct? Just wanted to get it confirmed before publishing the report.

Thank you in advance.