topic Re: PowerBI Table and Chart in DAX Commands and Tips

PowerBI Table and Chart

vvv — Sun, 19 Mar 2023 00:33:27 GMT

Hi, Below is my sample data and result needed, i am trying in calculated column but its giving count for each month , but not like i am expecting the result set.

Please help me with the DAX or Calculated column

I have Data Like This
ID	StartDate	EndDate
1	5/1/2022	5/31/2022
1	6/1/2022	6/30/2022
1	10/1/2022	10/30/2022
1	11/1/2022	11/30/2022
1	11/1/2022	11/30/2022
1	12/1/2022	12/31/2022
1	12/1/2022	12/31/2022
1	1/1/2023	1/31/2023


Result needed	Month Year	Count
	OCT 2022	3	5/1/2022 to 10/30/2022
	NOV-2022	5	5/1/2022 to 11/30/2022
	DEC-2022	7	5/1/2022 to 12/31/2022
	JAN-2023	8	5/1/2022 to 1/31/2023

Re: PowerBI Table and Chart

Greg_Deckler — Sun, 19 Mar 2023 03:23:29 GMT

vvv Better Running Total - Microsoft Power BI Community

Re: PowerBI Table and Chart

JW_van_Holst — Sun, 19 Mar 2023 13:21:39 GMT

This are the two tables I use:

and this is the DAX code

_result = VAR EOM = MAX(MonthYear[End of Month]) VAR _filter = FILTER(Transactions, Transactions[EndDate] <= EOM) RETURN COUNTROWS(_filter)

and this is the result:

Re: PowerBI Table and Chart

tamerj1 — Sun, 19 Mar 2023 15:19:58 GMT

Hi vvv

you have to have a Date table minimum with Date and Year Month columns. The you can use

Count =
SUMX (
VALUES ( 'Date'[Year Month] ),
CALCULATE (
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR MinDate =
MIN ( 'Date'[Date] )
RETURN
COUNTROWS (
FILTER ( 'Table', 'Table'[StartDate] <= MaxDate && 'Table'[EndDate] >= MinDate )
)
)
)

Re: PowerBI Table and Chart

vvv — Sun, 19 Mar 2023 16:01:53 GMT

Hi,

I did this but , i am getting same count count for all Months
Oct 22 - 8

Nov -22 8
Dec 22 8

Am i missing anything