topic Re: The current status of the card with granularity up to a week in DAX Commands and Tips

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

Lanik — Sun, 14 Jan 2024 10:31:11 GMT

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?

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

Fowmy — Sun, 14 Jan 2024 12:16:41 GMT

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:

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

Lanik — Sun, 14 Jan 2024 13:01:58 GMT

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

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

Fowmy — Sun, 14 Jan 2024 13:57:13 GMT

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

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

Lanik — Sun, 14 Jan 2024 16:02:29 GMT

But we have to countrows only with maxx status

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

Fowmy — Sun, 14 Jan 2024 19:52:55 GMT

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.

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

Lanik — Mon, 15 Jan 2024 05:21:04 GMT

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_noms5aFzHut2Q34MrQx5Dct7p6LMq7f3EhrGAGIdHwz4OK4Mpl-B/pubhtml
and here is PBI file: https://drive.google.com/file/d/1iGyOKzbkqsNdKLzEywCuZqaTTZW2NKIZ/view?usp=sharing