topic TotalYTD show 0 value when month value is 0 in DAX Commands and Tips

TotalYTD show 0 value when month value is 0

HaHothingoc — Wed, 14 Aug 2024 09:03:35 GMT

I have a problem with the TOTALYTD function in Power BI shown below picture:
I create a Measure Rate to calculation ratio of each month = (sum2 column/sum 1 column) *100

Then YTD Rate = TOTALYTD([rate],dim_month[Start of Month]). In the 2023-04 period, YTD rate = 0. The correct result must be equal to the 2023-03 period. Please help me.

Re: TotalYTD show 0 value when month value is 0

SachinNandanwar — Wed, 14 Aug 2024 15:32:30 GMT

This ?

Data >> Table Name : PreviousNonZero
Date,YTD_Rate
01-01-2013,0.50
02-01-2013,0.60
03-01-2013,0.90
04-01-2013,0.00
05-01-2013,1.20
06-01-2013,0.00
07-01-2013,0.00
08-01-2013,0.00
09-01-2013,1.82
10-01-2013,0.00
11-01-2013,0.00
12-01-2013,0.00

NonZeroValue = VAR curr_date = MAX ( 'PreviousNonZero'[Date] ) VAR SummarizeVar = FILTER ( SUMMARIZE ( ALLSELECTED ( 'PreviousNonZero' ), 'PreviousNonZero'[Date], "YTD_Rate", SUM ( 'PreviousNonZero'[YTD_Rate] ) ), [Date] < curr_date && [YTD_Rate] <> 0 ) VAR FilterVar = FILTER ( SummarizeVar, [YTD_Rate] >0) RETURN IF ( SUM ( 'PreviousNonZero'[YTD_Rate] ) = 0, MAXX ( FilterVar, [YTD_Rate] ), SUM ( 'PreviousNonZero'[YTD_Rate] ) )

Re: TotalYTD show 0 value when month value is 0

HaHothingoc — Thu, 15 Aug 2024 01:57:38 GMT

Thank you for your support.
That's exactly my expected result. The YTD Rate column is a measure and now it has the formula as below:
YTD Rate = TOTALYTD([rate],dim_month[Start of Month])
Can we modify the formula of the YTD Rate to get that result or it's just a be created new measure as you provide as above?

Re: TotalYTD show 0 value when month value is 0

SachinNandanwar — Thu, 15 Aug 2024 09:07:10 GMT

Just assign YTD Rate measure into a variable and use that variable in my measure.

Re: TotalYTD show 0 value when month value is 0

HaHothingoc — Sat, 17 Aug 2024 07:09:19 GMT

I have use this formula but the result as below:

Re: TotalYTD show 0 value when month value is 0

SachinNandanwar — Sat, 17 Aug 2024 20:26:16 GMT

Can you share your PBI file ?

Re: TotalYTD show 0 value when month value is 0

HaHothingoc — Mon, 19 Aug 2024 04:08:49 GMT

I sent the PBI file in a private message. Please check it in your box.

Re: TotalYTD show 0 value when month value is 0

Anonymous — Wed, 21 Aug 2024 08:21:34 GMT

Hi HaHothingoc

Could you tell me if your problem has been solved?

If it is, kindly Accept it as the solution. More people will benefit from it.

Or you are still confused about it, please provide me with more details about your problem.

Best Regards

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

Re: TotalYTD show 0 value when month value is 0

HaHothingoc — Wed, 21 Aug 2024 08:26:24 GMT

Dear Zhengdong Xu,

This issue is not resolved. Mr.Sachin is checking my pbix file.

Thanks you.

Re: TotalYTD show 0 value when month value is 0

SachinNandanwar — Wed, 21 Aug 2024 13:41:03 GMT

Hi HaHothingoc ,

The PBIX file that you shared had problems.

Please import the data into the file and then share it.

Re: TotalYTD show 0 value when month value is 0

HaHothingoc — Thu, 22 Aug 2024 06:39:36 GMT

Dear Mr.Sachin.

Thank you for your support.
While mirroring data to an Excel file and making a sample to send to you I solved this issue because the relationship in the semantic model is incorrect.

Regards,