topic Get the last date of the last month of a dataset in DAX Commands and Tips

Get the last date of the last month of a dataset

Anonymous — Tue, 25 Oct 2022 14:31:26 GMT

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 !

Re: Get the last date of the last month of a dataset

amitchandak — Tue, 25 Oct 2022 16:52:45 GMT

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

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

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

Re: Get the last date of the last month of a dataset

Anonymous — Wed, 26 Oct 2022 07:51:00 GMT

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

Re: Get the last date of the last month of a dataset

Anonymous — Tue, 01 Nov 2022 06:03:54 GMT

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