topic Cumulative percentage not showing proper data when used date column from fact table on weekend in DAX Commands and Tips

Cumulative percentage not showing proper data when used date column from fact table on weekend

SonaliDhotre — Wed, 29 Dec 2021 08:48:12 GMT

As mentioned in the subject, the cumulative percentage is showing correct data when the date column from the DimDate table is used, but incorrect data when the date column from the fact table is used.

When the date column is used from the Fact table

DAX:

Percentage Cumulated = TOTALMTD ( DIVIDE(SUM('Fact'[Shipped Quantity]),SUM('Fact'[Order Quantity]),0), DATEADD ( DATESMTD (Fact[Date]), 0, DAY ) )

Screenshot:

Note: The date on XAxis is also from the Fact table

When the date column is used from the DimDate table

DAX:

Percentage Cumulated = TOTALMTD ( DIVIDE(SUM('Fact'[Shipped Quantity]),SUM('Fact'[Order Quantity]),0), DATEADD ( DATESMTD (DimDate[Date]), 0, DAY ) )

Screenshot:

Note: Date on XAxis is also from DimDate table

Can anyone please tell me why this is happening?

Re: Cumulative percentage not showing proper data when used date column from fact table on weekend

amitchandak — Wed, 29 Dec 2021 08:58:34 GMT

SonaliDhotre , You should use date table, Do you need MTD? YTD ? or cumulative

Percentage MTD =
calculate (
DIVIDE(SUM('Fact'[Shipped Quantity]),SUM('Fact'[Order Quantity]),0),
DATESMTD (Date[Date])
)

or
Percentage YTD=
calculate (
DIVIDE(SUM('Fact'[Shipped Quantity]),SUM('Fact'[Order Quantity]),0),
DATESYTD (Date[Date])
)

or
Percentage Cumulated =
calculate (
DIVIDE(SUM('Fact'[Shipped Quantity]),SUM('Fact'[Order Quantity]),0),
filter(allselected('Date'),'Date'[date] <=max('Date'[date]))
)

if this does not help

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Cumulative percentage not showing proper data when used date column from fact table on weekend

V-lianl-msft — Mon, 03 Jan 2022 05:26:59 GMT

Hi SonaliDhotre ,

This is caused by the context of measure. In the two formulas you provided, the context is different. One is based on the DimDate table and the other is based on the date of the fact table. DATEADD is a time intelligence function.The result table includes only dates that exist in the dates column.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.