How to get the value at the latest date, where values are not 0?

[sonya7]

hello, I want to show the last entered value instead of zero values. I was able to find the total number as far as I tried. how can i get the last value?

[sonya7]

When I add a filter to the measurement, the month I added disappears because that month is empty. how can i solve this? @Ashish_Mathur 

[Ashish_Mathur]

Hi,

I cannot understand your question.  Clearly show the problem and also the expected result.

[v-luwang-msft]

Hi @sonya7 ,

Create a new column ,and change it to number:

Yearmon = _MonthlyReceipt[Year]&""&0&""&_MonthlyReceipt[Month]

Test the measure like below:

test =
VAR test =
    CALCULATE (
        MAX ( _MonthlyReceipt[Yearmon] ),
        FILTER ( ALL ( _MonthlyReceipt ), _MonthlyReceipt[Receipt] <> 0 )
    )
RETURN
    CALCULATE (
        MAX ( _MonthlyReceipt[Receipt] ),
        FILTER ( ALL ( _MonthlyReceipt ), _MonthlyReceipt[Yearmon] = test )
    )

Output result:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[sonya7]

yes it's true but what I want is to print the last filled value to the next empty months, they should remain themselves or the previous months themselves. 

[sonya7]

and when i try i see this

[lbendlin]

This is different from your original question.  Zero (0) and BLANK() (null) are different things and need to be handled differently.  Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[sonya7]

this is what i want, thanks 

[v-luwang-msft]

Hi @sonya7 ,

Adjust the measure to the below:

test = 
VAR test =
    CALCULATE (
        MAX ( _MonthlyReceipt[Yearmon] ),
        FILTER ( ALL ( _MonthlyReceipt ), _MonthlyReceipt[Receipt] <> 0 )
    )
RETURN   IF(MAX(_MonthlyReceipt[Receipt])<>0,MAX(_MonthlyReceipt[Receipt]),
    CALCULATE (
        MAX ( _MonthlyReceipt[Receipt] ),
        FILTER ( ALL ( _MonthlyReceipt ), _MonthlyReceipt[Yearmon] = test )
    ))

Output result:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[sonya7]

Since there is no data entered after the 4th month in my montlyreceipt table, I take the date from my dimdate table. I think I'm having trouble here.  and I get a result like this. how can we solve this?  By the way, I want the total to be the sum of the numbers up to the month we are. Thanks

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[sonya7]

here is my report: 

https://1drv.ms/u/s!AuRc6JlNFdIAhnN0SRSbVRMOTOQD?e=ZmKutr

[Ashish_Mathur]

Hi,

Download the PBI file from here.

Hope this helps.

[sonya7]

this worked! thank you. But I want to add all the numbers in total

[Ashish_Mathur]

You are welcome.  What figure are you expecting in the Total row?  Please specify clearly.

[sonya7]

I want the sum of all the months I selected in the total number because I will now use it as a receipt. So 37.935.583+(2.168.400*8)

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[sonya7]

it was very good and fast! best regards

[Ashish_Mathur]

Thank you.

[lbendlin]

test = 
var d = max(DimDate[TheDate])
var l = calculate(max(_MonthlyReceipt[TransactionDate]),all(_MonthlyReceipt),_MonthlyReceipt[TransactionDate]<=d)
return  if(year(d)*100+month(d)<>year(l)*100+month(l),CALCULATE(sum(_MonthlyReceipt[Receipt]),all(_MonthlyReceipt),_MonthlyReceipt[TransactionDate]=l))

[sonya7]

hi, i tried your size but i couldn't get the result i wanted. thank you so much