TotalYTD show 0 value when month value is 0

[HaHothingoc]

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.

[SachinNandanwar · Accepted solution]

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

[HaHothingoc · Accepted solution]

I have use this formula but the result as below:

[SachinNandanwar]

Can you share your PBI file ?

[HaHothingoc]

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

[Anonymous]

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.

[HaHothingoc]

Dear Zhengdong Xu,

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

Thanks you.

[SachinNandanwar]

Hi @HaHothingoc ,

The PBIX file that you shared had problems.

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

[HaHothingoc]

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,

[SachinNandanwar · Accepted solution]

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

[HaHothingoc]

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?

[SachinNandanwar]

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