Get the last date of the last month of a dataset

[Anonymous]

Hi,  

I try to calculate the last date from my dataset for the last month, the formula works : 

Measure = CALCULATE(LASTDATE(Inventory[History_Date]),MONTH(Inventory[History_Date])=MONTH(MAX(Inventory[History_Date]) - 1))

(on the 10/24/2022, returns the 09/28/2022 (last date of the last month in my dataset) )

However, when i use the measure in an other  CALCULATE formula, the result is wrong : the measure is interpreted as the last date of the current month instead if the last month

Test formula :

Nb_devices_M-1 = CALCULATE(DISTINCTCOUNT(Inventory[ID]),FILTER(Inventory,Inventory[History_Date]=[Measure]))

(on the 10/24/2022, returns the number of ID on the 10/24/2022, instead of the 09/28/2022 (last date of the last month in my dataset) )

Any idea ? 🤔

Thanks !

[v-yiruan-msft]

Hi  @Anonymous ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

Please update the formula of measure [Nb_devices_M-1] as below and check if it can return the correct result:

Nb_devices_M-1 =
VAR _lastdate = [Measure]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Inventory[ID] ),
        FILTER ( Inventory, Inventory[History_Date] = _lastdate )
    )

If the above one can't help you get the desired result, please provide some sample data in your table 'Inventory' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak]

@Anonymous , openingbalancemonth of the month gives the value of last date of last month

You have to use the date table joined with History_Date

calculate(DISTINCTCOUNT(Inventory[ID]), openingbalancemonth (Date[Date]))

or

calculate(lastnonblankvalues(Inventory[History_Date], DISTINCTCOUNT(Inventory[ID])), previousmonth(Date[Date]))

Power BI Allocating Targets: openingbalancemonth, openingbalancequarter, openingbalanceyear: https://youtu.be/6lzYOXI5wfo