Forum Discussion
Helper IICalculate value between selected date one month before and selected slicer date
Hello everyone,
I need help for the below mentioned scenario:
Lets say DIMDate selected on slicer : 2-25-2019
Need to Calculate NetRentYOY =
((SUM(NetRent) from 1/25/2019 to 2/25/2019)-(SUM(NetRent) from 1/25/2018 to 2/25/2018))
/(SUM(NetRent) from 1/25/2018 to 2/25/2018)
How to calculate NetRent based on date between sartdate and End date --(SUM(NetRent) from 1/25/2019 to 2/25/2019)
How to Calculate filter and sum for LY--(SUM(NetRent) from 1/25/2018 to 2/25/2018)
Thanks for help in advance!
Thanks,
Neha
Hi NehaSha ,
Make sure there existing an active relationship between DIMDate and fact data table based on [Date] field.
New measures as below:
NetRent within one month TY = CALCULATE ( SUM ( Data[NetRent] ), DATESINPERIOD ( DIMDate[Date], SELECTEDVALUE ( DIMDate[Date] ), -1, MONTH ) ) NetRent within one Month LY = CALCULATE ( [NetRent within one month TY], SAMEPERIODLASTYEAR ( DIMDate[Date] ) ) NetRentYOY = [NetRent within one month TY]-[NetRent within one Month LY]Best regards,
Yuliana Gu
1 Reply
v-yulgu-msftMicrosoft Employee
Hi NehaSha ,
Make sure there existing an active relationship between DIMDate and fact data table based on [Date] field.
New measures as below:
NetRent within one month TY = CALCULATE ( SUM ( Data[NetRent] ), DATESINPERIOD ( DIMDate[Date], SELECTEDVALUE ( DIMDate[Date] ), -1, MONTH ) ) NetRent within one Month LY = CALCULATE ( [NetRent within one month TY], SAMEPERIODLASTYEAR ( DIMDate[Date] ) ) NetRentYOY = [NetRent within one month TY]-[NetRent within one Month LY]Best regards,
Yuliana Gu