topic Re: Need an uregent help with cumulative DAX formula in DAX Commands and Tips

Need an uregent help with cumulative DAX formula

Srinivas904 — Tue, 25 Mar 2025 09:05:46 GMT

Hi,

I am using the measure below to calculate the Pricing Total. This measure takes the sum. I am trying to calculate the cumulative value for this measure using another measure, but I am getting all 0 values.

Can anyone please help me?

YTD Pricing Total =

SUMX(

VALUES(Fact_Transactions[Product]),

IF(

ISBLANK([Prior Year Qty]) || [Prior Year Qty] = 0 || ISBLANK([Volume_V1]) || [Volume_V1] = 0,

([ASP_V1] - [Last year_V1]) * [Volume_V1]

)

)

Cumulative YTD Pricing =
VAR SelectedYear = MAX(DIM_TIME[FIN_YEAR])
RETURN
CALCULATE(
[YTD Pricing Total],
FILTER(
ALL(DIM_TIME),
DIM_TIME[Full_Date] <= MAX(DIM_TIME[Full_Date]) &&
DIM_TIME[FIN_YEAR] = SelectedYear
)
)

Thanks

Srinivas

Re: Need an uregent help with cumulative DAX formula

bhanu_gautam — Tue, 25 Mar 2025 09:22:23 GMT

Srinivas904 The issue with your Cumulative YTD Pricing measure might be related to the context in which the DIM_TIME table is being filtered.

DAX
Cumulative YTD Pricing =
VAR SelectedYear = MAX(DIM_TIME[FIN_YEAR])
RETURN
CALCULATE(
[YTD Pricing Total],
FILTER(
ALL(DIM_TIME),
DIM_TIME[Full_Date] <= MAX(DIM_TIME[Full_Date]) &&
DIM_TIME[FIN_YEAR] = SelectedYear
)
)

Re: Need an uregent help with cumulative DAX formula

v-hashadapu — Thu, 27 Mar 2025 05:43:36 GMT

Hi Srinivas904 , Thank you for reaching out to the Microsoft Community Forum.

Your Cumulative YTD Pricing measure is returning 0s because of how the filter context is applied. The original use of ALL(DIM_TIME) might be removing too many filters, please try below:
Cumulative YTD Pricing =

VAR SelectedYear = MAX(DIM_TIME[FIN_YEAR])

RETURN

CALCULATE(

[YTD Pricing Total],

FILTER(

ALLSELECTED(DIM_TIME[Full_Date]),

DIM_TIME[Full_Date] <= MAX(DIM_TIME[Full_Date]) &&

DIM_TIME[FIN_YEAR] = SelectedYear

)

Make sure Fact_Transactions links to DIM_TIME[Full_Date] correctly, and check your underlying measures ([Prior Year Qty], [Volume_V1], etc.) aren’t returning blanks or 0s.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

Re: Need an uregent help with cumulative DAX formula

v-hashadapu — Thu, 03 Apr 2025 05:11:05 GMT

Hi Srinivas904 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

Re: Need an uregent help with cumulative DAX formula

Srinivas904 — Thu, 03 Apr 2025 13:14:30 GMT

Hi v-hashadapu , actually my entire approach was wrong, i found the solution for it, how can i delete this post ?

Thanks

Re: Need an uregent help with cumulative DAX formula

v-hashadapu — Fri, 04 Apr 2025 04:34:14 GMT

Hi Srinivas904 , thanks for the update and happy to know you have found a solution. As to the deletion, there is no need for it. You can simply post the details of your approach and mark it 'Accept as Solution'. It may help others who may be in similar scenarios. Thank you.

Re: Need an uregent help with cumulative DAX formula

v-dineshya — Tue, 08 Apr 2025 12:07:23 GMT

Hi Srinivas904 ,

Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.

Thank you.

Re: Need an uregent help with cumulative DAX formula

v-dineshya — Tue, 15 Apr 2025 07:17:46 GMT

Hi Srinivas904 ,

Thanks for the update and happy to know you have found a solution. As to the deletion, Please check the below Forum community threads.

How can i delete my own post in forum? - Microsoft Fabric Community

Contributing to the Community - Microsoft Fabric Community

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

Re: Need an uregent help with cumulative DAX formula

v-dineshya — Thu, 24 Apr 2025 07:10:11 GMT

Hi Srinivas904 ,

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards