topic Re: Consecutive month actual under budget in each fiscal year in DAX Commands and Tips

Consecutive month actual under budget in each fiscal year

Anonymous — Tue, 19 Jul 2022 11:21:26 GMT

Hi Microsoft Forum Contributors and/or tamerj1,

I'm trying to produce a measure called "Consecutive Month Under Budget" that gets its value from how many fiscal months in a row the month actual is below the month budget within a fiscal year (and then resets the calculation again for a new fiscal year).

Here's my sample data to help clarify my question:
Dimensions 1, 2, and Dim Date all have a 1 to many relationship with the fact table.
What's the best DAX to get this result? If it is easier, I can also merge the 1st and 2nd Dimension columns into the fact table so that the only 1 to many relationship the fact table has is just with the Dim Date.

Value from 1st Dimension Column	Value from 2nd Dimension Column	Fiscal Year from DimDate	Fiscal Month from DimDate	Budget	Actual	Consecutive Month Under Budget
A	D	2021	1	10	11	0
A	D	2021	2	10	10	0
A	D	2021	3	10	9	1
A	D	2021	4	10	9	2
A	D	2021	5	10	8	3
A	D	2021	6	10	15	0
A	D	2021	7	10	10	0
A	D	2021	8	10	12	0
A	D	2021	9	10	8	1
A	D	2021	10	10	7	2
A	D	2021	11	10	2	3
A	D	2021	12	10	8	4
A	D	2022	1	50	40	1
A	D	2022	2	50	30	2
A	D	2022	3	50	55	0
A	D	2022	4	50	60	0
A	D	2022	5	50	40	1
A	D	2022	6	50	25	2
A	D	2022	7	50	70	0
A	D	2022	8	50	40	1
A	D	2022	9	50	80	0
A	D	2022	10	50	90	0
A	D	2022	11	50	80	0
A	D	2022	12	50	40	1
A	C	2021	1	20	15	1
A	C	2021	2	20	15	2
A	C	2021	3	20	10	3
A	C	2022	1	10	5	1
A	C	2022	2	10	15	0
A	C	2022	3	10	10	0
B	C	2022	1	70	10	1
B	C	2022	2	70	80	0
B	C	2022	3	70	50	1

Thanks so much in advance.

Re: Consecutive month actual under budget in each fiscal year

tamerj1 — Tue, 19 Jul 2022 12:13:35 GMT

Hi Anonymous
Thank you for posting this question. Please provide the sample data from each table seperately. Thank you

Re: Consecutive month actual under budget in each fiscal year

Anonymous — Mon, 25 Jul 2022 07:21:11 GMT

Hi Anonymous,

Any update for these? It seems like you share the sample data that concatenate with multiple table fields, they did not help us to clarify your table structure.

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: Consecutive month actual under budget in each fiscal year

Anonymous — Tue, 26 Jul 2022 03:49:29 GMT

Okay, Thanks for your replies Anonymous and tamerj1
I understand that I have to provide the sample data in a tidier fashion so I will do this as soon as possible.
I was just having trouble attaching file here, what's your recommended solution for uploading file?