cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SoniaP
Regular Visitor

Get the last date of the last month of a dataset

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 !
1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

Hi @SoniaP ,

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

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

View solution in original post

3 REPLIES 3
yingyinr
Community Support
Community Support

Hi  @SoniaP ,

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
yingyinr
Community Support
Community Support

Hi @SoniaP ,

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@SoniaP , 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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors