Need an uregent help with cumulative DAX formula

[Srinivas904]

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,

        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

[Srinivas904 · Accepted solution]

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

Thanks

[v-hashadapu]

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.

[Srinivas904 · Accepted solution]

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

Thanks

[v-dineshya · Accepted solution]

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

[v-dineshya]

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

[v-dineshya]

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.

[v-hashadapu]

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.

[v-hashadapu]

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.

[bhanu_gautam]

@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
)
)