Search code examples
powerbidax

How can I calculate the number of IDs for the previous month?


I have a table name Legal that have Restaurant ID, Opening date and Closing columns. This table has two inactive relationshionships with Calendar through Calendar[Date].

I'm trying to calculate the number of IDs open between a date range. An ID is consider to be open if it has Opening but no Closing or if closing is less than the analyzed time; and it's closed if Closing date is greater than date analyzed.

So far I'm calculating like this:

Opening = 
    CALCULATE(
        COUNT(Legal[Restaurant ID]),
        Legal[Opening date]<>BLANK(),
        USERELATIONSHIP('Calendar'[Date],Legal[Opening date]),
        FILTER('Calendar','Calendar'[Date]<=TODAY()),
        FILTER(ALL(Legal),Legal[Opening date] = PREVIOUSMONTH('Calendar'[Date]))
)
Closing = 
    CALCULATE(
        COUNT(Legal[Restaurant ID]),
        USERELATIONSHIP('Calendar'[Date],Legal[Closing]),
        FILTER('Calendar','Calendar'[Date]<=TODAY())
)
Net = [Opening] - [Closing]
TotalOpen = 
VAR MaxCalendar = MAX(Calendar[Date])
VAR MaxSalesMonth = CALCULATE(MAX('Calendar'[Month]), ALL('Calendar'), 'Calendar'[Date] = MaxCalendar)
VAR MinCalendar = CALCULATE(MIN(Legal[Opening date]), REMOVEFILTERS('Calendar'))
VAR Restaurants =
    CALCULATE([Net],
        DATESBETWEEN('Calendar'[Date], MinCalendar, MaxCalendar),
        USERELATIONSHIP(Legal[Restaurant ID], 'Restaurants Dim'[Restaurant ID])
    )
RETURN
    IF(MAX('Calendar'[Month]) = MaxSalesMonth, Restaurants, BLANK())

I've try to use FILTER(ALL(Legal), Legal[Opening date] = PREVIOUS(Legal[Opening date]) to calculate the number of IDs opened last month.

The idea is that when I analyzed this in a table I want to retrieve the Total opened IDs I had the previous month something like this:

MONTH Total Open Total Open Previous Month
January 100 99
Febreruary 103 100
March 105 103
April 102 105

Solution

  • It was resolved using this

    Total Open Previous Month =
    VAR MaxCalendar = MAX(Calendar[Date])
    VAR MinCalendarPreviousMonth = CALCULATE(MIN(Calendar[Date]), PREVIOUSMONTH(Calendar[Date]))
    VAR MaxCalendarPreviousMonth = CALCULATE(MAX(Calendar[Date]), PREVIOUSMONTH(Calendar[Date]))
    VAR RestaurantsPreviousMonth =
        CALCULATE(
            [Net],
            DATESBETWEEN(Calendar[Date], MinCalendarPreviousMonth, MaxCalendarPreviousMonth),
            USERELATIONSHIP(Legal[Opening date], Calendar[Date])
        )
    RETURN
        RestaurantsPreviousMonth
    

    This measure will calculate the count of IDs opened in the previous month based on the relationship between the Legal table's opening date and the Calendar table. Make sure that your Calendar table includes a full range of dates including the previous month's dates for accurate calculations.

    You can then use this measure in your table alongside the current month's total open count. Your table should display the desired output showing the total open IDs for each month along with the total open IDs for the previous month.