Need Help on DAX measure

[snarasi_83]

Hi,

I have fair knowledge in Power BI , not able to figure out one piece of puzzle.

I have a simple table below data

Sample Data:

enddate	status	Brand	Due
11/2/2022	Failed	Lenova	1
12/10/2022	Failed	Microsoft	1
1/22/2023	Failed	Microsoft	1
3/10/2023	Failed	Microsoft	1
3/28/2023	Complete	Microsoft	1
3/30/2023	Ontrack	Microsoft	1
4/10/2023	Ontrack	Lenova	1
4/17/2023	Risk	Lenova	1
5/31/2023	Risk	Microsoft	1
5/30/2023	Ontrack	HP	1
5/10/2023	Ontrack	Lenova	1
11/10/2023	Risk	Microsoft	1
12/10/2023	InProgress	HP	1
1/10/2024	Risk	Microsoft	1

Expected Result :

Clients wants count of status by Months . (Conditions are)

• Anything less than current month (3/1/2023) should be summed by status as 1 row. 
• Current Month and future months row wise.
• Entere table is filtered for previous 3 months , Current and next 3 months (Total 7 Months)

Please attach pbix file if u can resolve 

[ppm1 · Accepted solution]

Here's one approach. You can add a calculated column with the expression below and then use it in your visual (filtering out the two More Than values). You'll also need to work out a sort-by column to get them in your desired order.

MonthForVisual =
VAR thisSOM =
    EOMONTH ( 'Status'[enddate], -1 ) + 1
VAR todaySOM =
    EOMONTH ( TODAY (), -1 ) + 1
VAR MonthsDiff =
    DATEDIFF ( todaySOM, thisSOM, MONTH )
RETURN
    SWITCH (
        TRUE (),
        MonthsDiff < -3, "MT 3 Mos Ago",
        MonthsDiff < 0, "Prev 3 Mos",
        MonthsDiff < 4, FORMAT ( 'Status'[enddate], "MMM" ),
        "MT 3 Mos From Now"
    )

Pat

[snarasi_83]

Awesome got your solution . it works . 

Any simple solution it can be sorted.

[Greg_Deckler]

@snarasi_83 You'll need to use a disconnected table as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[snarasi_83]

@Greg_Deckler  I tried but i am not able to figure out the first condition.

• Anything less than current month (3/1/2023) should be summed by status as 1 row. 

Any other option ? or implementation . If solved please upload pbix file