Forum Discussion
Solved
Power BI measure
https://docs.google.com/spreadsheets/d/1qnh07BRHlbXaGnLkgahf95uXpkDlXgUq5oKS866OMoo/edit?usp=sharing
Hi all,
Please help i have attached a link below for the excel sheet i need help with creating a measure that will calculate the columns that are highlighted in blue in the attached excel link
Thanks
Hi Xirhandziwa,
You can try to use the following measure formulas to achieve your requirement:
mat 2021 = VAR currDate = MAX ( 'Table'[Date] ) RETURN CALCULATE ( SUM ( 'Table'[2020/2021] ), FILTER ( ALLSELECTED ( 'Table' ), [Date] > currDate ) ) + CALCULATE ( SUM ( 'Table'[2021/2022] ), FILTER ( ALLSELECTED ( 'Table' ), [Date] <= currDate ) ) mat 2022 = VAR currDate = MAX ( 'Table'[Date] ) RETURN CALCULATE ( SUM ( 'Table'[2021/2022] ), FILTER ( ALLSELECTED ( 'Table' ), [Date] > currDate ) ) + CALCULATE ( SUM ( 'Table'[2022/2023] ), FILTER ( ALLSELECTED ( 'Table' ), [Date] <= currDate ) ) diff = [mat 2022]-[mat 2021]Regards,
Xiaoxin Sheng
3 Replies
Hi Xirhandziwa,
You can try to use the following measure formulas to achieve your requirement:
mat 2021 = VAR currDate = MAX ( 'Table'[Date] ) RETURN CALCULATE ( SUM ( 'Table'[2020/2021] ), FILTER ( ALLSELECTED ( 'Table' ), [Date] > currDate ) ) + CALCULATE ( SUM ( 'Table'[2021/2022] ), FILTER ( ALLSELECTED ( 'Table' ), [Date] <= currDate ) ) mat 2022 = VAR currDate = MAX ( 'Table'[Date] ) RETURN CALCULATE ( SUM ( 'Table'[2021/2022] ), FILTER ( ALLSELECTED ( 'Table' ), [Date] > currDate ) ) + CALCULATE ( SUM ( 'Table'[2022/2023] ), FILTER ( ALLSELECTED ( 'Table' ), [Date] <= currDate ) ) diff = [mat 2022]-[mat 2021]Regards,
Xiaoxin Sheng
Hi Anonymous Thank you so much, this worked i really appreciate your help 🤗
Hi Anonymous Thank you so much, this worked i really appreciate your help 🤗
