topic Calculate formula in Power BI in DAX Commands and Tips

Calculate formula in Power BI

SriGanesh — Thu, 11 Jul 2024 09:57:45 GMT

I have use the below Formula in Power BI and it is working fine if i choose 2023 and 2024 year both. If i choose alone 2024. It is not sum the Previous 2 months ie. 2023 year Nov and Dec.

Power BI formula I used-

CALCULATE(SUM('REPAIR_UNIT_SUMMARY'[OBF_NUMERATOR]),DATESINPERIOD('REPAIR_UNIT_SUMMARY'[MONTHYEAR],LASTDATE('REPAIR_UNIT_SUMMARY'[MONTHYEAR]),-3,MONTH)).

help required to reslove this issues

Re: Calculate formula in Power BI

bhanu_gautam — Thu, 11 Jul 2024 10:30:43 GMT

SriGanesh , Try using this formula this uses ALLfunction

CALCULATE(
SUM('REPAIR_UNIT_SUMMARY'[OBF_NUMERATOR]),
DATESINPERIOD(
'REPAIR_UNIT_SUMMARY'[MONTHYEAR],
LASTDATE(ALL('REPAIR_UNIT_SUMMARY'[MONTHYEAR])),
-3,
MONTH
)
)

Re: Calculate formula in Power BI

SriGanesh — Thu, 11 Jul 2024 11:14:19 GMT

It is calculating the Same value for all the rows.

Re: Calculate formula in Power BI

bhanu_gautam — Thu, 11 Jul 2024 11:22:42 GMT

DAX
CALCULATE(
SUM('REPAIR_UNIT_SUMMARY'[OBF_NUMERATOR]),
DATESINPERIOD(
'REPAIR_UNIT_SUMMARY'[MONTHYEAR],
LASTDATE('REPAIR_UNIT_SUMMARY'[MONTHYEAR]),
-3,
MONTH
),
PARALLELPERIOD(
'REPAIR_UNIT_SUMMARY'[MONTHYEAR],
-1,
YEAR
)
),

Try using this or share sample date