Having problems to get the value of the last day

[Anonymous]

Hello guys, hope you're doing well.


Basically I have a Order table that has date filled in, so I can't use LASTDAY function since it has duplicated dates because there many order codes in a single day. So my first attempt was CALCULATE(Measure'[Value],MAX(Order[Date]) but it occurs a error saying that can't work with TRUE/FALSE conditions in this expression. So I also tried:

LASTNONBLANK(Order'[Date],Measure'[Value]), but it only brought the first row and what I need is the the values of the whole day, because as I said there are many order codes in the same day. 

How can i make it out?

Thanks!

[Anonymous]

Hi  @Anonymous  ,

According to your description, I create this data：

Here are the steps you can follow：

1. Create measure.

IF_max_date =
var _1=CALCULATE(MAX('Table (2)'[date]),ALL('Table (2)'))
return
IF(MAX('Table (2)'[date])=_1,1,0)

2. Drag IF_max_date to Filters on this visual of Filters

Choose is =1

3. Result：

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table and build a relationship from the Date column of the Order Table to the Date column of the Calendar Table.  Write these measures:

Total = SUM(Data[Balance])

Balance as on last day = CALCULATE([Total],LASTNONBLANK('Calendar'[Date],CALCULATE([Total])))

Drag the second measure to your card visual.

Hope this helps.

[Anonymous]

This actually helped, thank you very much.

[Ashish_Mathur]

You are welcome.

[lbendlin]

your first attempt wasn't that bad.

Use variables and solve your problem in steps.

- create a variable that finds the max day

- calculate the order value for all orders with a filter on that day variable

- return the result