The current status of the card with granularity up to a week

[Lanik]

Hello everybody!
It looks like I need help with the DAX code..
I have a table with columns card_id, status, start_date (when the status starts) and end_date (when the status ends) and Calendar.

I have to show a visualization granularity: year - quarter - month - week with counting cards by actual status at the selected hierarchy level. Without date slicer.
I wrote a measure:
Count =
var _MinDate = MIN('Calendar'[date])
var _MaxDate = MAX('Calendar'[date])
var _result =
CALCULATE(
DISTINCTCOUNT('Card'[card_id]),
'Card'[start_date] <= _MaxDate,
'Card'[end_date] > _MinDate,
FILTERS('Card'[status]),
REMOVEFILTERS('Calendar')
)
return
_result

but it doesn't work the way I need it to... It shows 1 for each status, but I need the latest status according to the selected granularity.
I also tried to use dynamic ranking for the latest card's status, but it didn't work..
Do you have any ideas how to solve this?

[Fowmy]

@Lanik 

If you have created a relationship between your table and the calendar table, remove it and add this measure:

Current Status = 
VAR __MinDate = MIN('Calendar Auto'[Date] )
VAR __MaxDate = MAX('Calendar Auto'[Date] )
VAR __Result = 
   
   MAXX( 
        FILTER(
                'Table',
                'Table'[start_date] >= __MinDate &&
                'Table'[end_date] <= __MaxDate
        ),
        'Table'[status]
    )

RETURN
    __Result

Result, change the dates to see the current status by ID: File is attached below:

[Lanik]

Thanks! But that's not exactly what I need..  My main goal is to show on a stacked column chart the number of cards by actual status. Like this for card_id = 1

[Fowmy]

@Lanik 

I modified the measure, use Status on the Legend :

Current Status = 
VAR __MinDate = MIN('Calendar Auto'[Date] )
VAR __MaxDate = MAX('Calendar Auto'[Date] )
VAR __Result = 

    COUNTROWS( 
        FILTER(
                'Table',
                'Table'[start_date] >= __MinDate &&
                'Table'[end_date] <= __MaxDate
        )
    )   

RETURN
    __Result

[Lanik]

But we have to countrows only with maxx status

[Fowmy]

@Lanik 

Share some more sample or dummy data with the expected result.
You can either paste your data in the reply box or save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.

[Lanik]

I have two tables: a calendar (I need a time hierarchy for a stacked column chart) and a table with cards and their status changes. We do not use a calendar slicer, only a hierarchy for a stacked column chart.
I expect my stacked column chart with a level hierarchy (year - quarter - months - weeks) will show me the number of cards with the latest status at the selected level of hierarchy.
For examle, in 2023 I expected 2 cards, one with the closed status and the second with the active status (because card_id = 1 had status closed on 31.12.2023 and card_id =2 had status active on  31.12.2023), but if  I go to the next level of time hierarchy the card status may differ because the last day at this hierarchy level will be 31.03.2023 for quarter 1, 30.06.2023 for quarter 2 and etc.
Now my measure calculates the number of cards with all statuses, and not with the last status at the selected hierarchy level..
Thank you for your help and I'm sorry if my explanation is not very clear.
Here is a link to the tables with data https://docs.google.com/spreadsheets/d/e/2PACX-1vRZnle7DXDpVOb4jLKc228GCv6_noms5aFzHut2Q34MrQx5Dct7p...
and here is PBI file:  https://drive.google.com/file/d/1iGyOKzbkqsNdKLzEywCuZqaTTZW2NKIZ/view?usp=sharing