cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Imagauthamam
Helper I
Helper I

Need Help in LY and TY Measure based on a slicer

Hi Community,

I'm a beginner in Power BI. Need help in 2 measure calculations.

I'm just showing LY and TY comparison. This works fine independently.

But when we put a Year slicer, this doesnot work as expected.

Below, are my measure calculations:

Total Records LY =
Var Year = YEAR(MAX([activity_date]))-1
return CALCULATE(COUNTROWS('Fact'),'Calendar'[Year]=Year)
Total Records TY =
Var Year = YEAR(MAX([activity_date]))
return CALCULATE(COUNTROWS('Fact'),'Calendar'[Year]=Year)
 
Below are my requirements:
If we select 2023 in Year slicer, the CY measure should give the data for entire 2023 and LY measure should give the data for entire 2022. From the below screenshot, there is no data after June for LY measure even though data is available for entire 2022
Similarly, If I select 2022 in Year slicer, CY should give 2022 data and LY should give 2021 data and all the months  should be visible in X axis

 

Screenshot 2023-06-06 140958.jpg

1 ACCEPTED SOLUTION

@Dhairya I tried this but it was not working for me.

Meanwhile I have modified the measure to get the required output

 

add a Year Slicer, make it single select (from date dimension)
add a chart with Month (from date dimension) on x and 2 measure (Total Records , Total Records LY)

Total Records = COUNTROWS('Fact')
Total Records LY = CALCULATE([Total Records], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

View solution in original post

4 REPLIES 4
Imagauthamam
Helper I
Helper I

Hi @Dhairya 

Thanks for the reply. But I'm getting the error:

Screenshot 2023-06-06 155725.jpg

Have you created a seperate date table named Calendar?
If yes then  the above year is for dataytype of that Calendar'date' column,
In PREVIOUSYEAR function you have to pass field having 'date' datatype. 

@Dhairya I tried this but it was not working for me.

Meanwhile I have modified the measure to get the required output

 

add a Year Slicer, make it single select (from date dimension)
add a chart with Month (from date dimension) on x and 2 measure (Total Records , Total Records LY)

Total Records = COUNTROWS('Fact')
Total Records LY = CALCULATE([Total Records], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Dhairya
Solution Supplier
Solution Supplier

Can you please try below measure to calculate previous year data

TOTAL RECORDS LY = CALCULATE(COUNTROWS('Fact'), PREVIOUSYEAR('Calendar'[Year]))

Please mark my solution as accepted if it worked for you!

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors