DAX Measure

[ToussaintSenin]

Hi there. I am reaching out to seek your help with one of the issue I am having with calculatimg the distinct number of test done in a finanacial Month. my financial calendar table starts 1st Apr of each year and the financial week starts on the 1st Monday of each week. for example the last weekof April 24 runs 29/04/24 to 05/05/24 which means any tests done on (01/05/24 - 05/05/24) are counted as Apr-24 test. I have attached the test-fact table and the financial months breakdown to consider for your information.

Best regards

Location of break point	Date	Officer	Comments	Scheme Name
Block 49 - 56	05/04/2024		Test ok GK	Liora Court
Block 1-8	10/04/2024		Test ok GK	Liora Court
main entrance	16/04/2024	AN	all ok	Liora Court
Block 9-15	24/04/2024		Test ok GK	Liora Court
Fault on fire panel	01/05/2024		Engineer attended - Openview	Liora Court
Block 18-24	01/05/2024		Test ok GK	Liora Court
Block 25-32	08/05/2024		Test ok GK	Liora Court
main entrance	16/05/2024		Test ok GK	Liora Court
Block 33-44	22/05/2024		Southern monitoring , test ok	Liora Court
Communal lounge	30/05/2024		Test ok GK	Liora Court
Block 49 - 56	5 Jun 24		Test ok GK	Liora Court
Block 33-44	19 Jun 24		Test OK MS	Liora Court
Block 25-32	26 Jun 24		Test OK MS	Liora Court
Small back lounge	4 Jul 24	AN	test okay	Liora Court
Main entrance	9 Jul 24		Test ok - JR	Liora Court
main entrance	26 Jul 24		Test ok - GK	Liora Court
Block 1-8	1 Aug 24		Test OK MS	Liora Court
Small back lounge	6 Aug 24		Test ok - JR	Liora Court
Main entrance	13 Aug 24		Test ok - JR	Liora Court
Communal lounge	22 Aug 24		Test ok - JR	Liora Court
Small back lounge	30 Aug 24		Test ok - JR	Liora Court
Main entrance	5 Sep 24		Test ok - JR	Liora Court
Communal lounge	13 Sep 24		Test ok - JR	Liora Court
Small back lounge	16 Sep 24		Test ok - JR	Liora Court
Main entrance	26 Sep 24		Test ok - JR	Liora Court
Communal lounge	4 Oct 24		Test ok - JR	Liora Court
Small back lounge	8 Oct 24		Test ok - JR	Liora Court
Main entrance	15 Oct 24		Test ok - JR	Liora Court
Communal lounge	21 Oct 24		Test ok - JR	Liora Court
Small back lounge	1 Nov 24		Test ok - JR	Liora Court
Outside flat 15	5 Nov 24		Test ok - JR	Liora Court
Main entrance	15 Nov 24		Test ok - JR	Liora Court

[johnt75 · Accepted solution]

I think you might find https://www.sqlbi.com/articles/using-weekly-calendars-in-power-bi/ useful.

[Kedar_Pande]

@ToussaintSenin 

Create a measure:

Distinct Tests in Financial Month =
CALCULATE(
DISTINCTCOUNT('Test Fact'[Location of break point]),
FILTER(
'Financial Calendar',
'Financial Calendar'[Week Start Date] <= MAX('Test Fact'[Date]) &&
'Financial Calendar'[Week End Date] >= MIN('Test Fact'[Date])
)
)

💌 If this helped, a Kudos 👍 or Solution mark ✔️ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Anonymous · Accepted solution]

Hi @ToussaintSenin ,

Please follow the steps below:

1. Create the start date of the financial year. If the month is less than 4, the start date is 1^st Apr of previous year. If it is greater than or equal to 4, it is 1^st April of the current year.

2. Financial week starts on the 1^st Monday of each week. Create a column to list the first day of each week, make sure the calculation is only based on Week and Year in Date table.

3. Don’t forget to create a relationship between the two tables.

4. Create a visualization, it calculates the all-test counts of each financial week in the financial Month you provided.

5. We could also check the counts in the month you select.

Best regards,

Lucy Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ToussaintSenin]

Hi There

Thank you very much for you answer. It works very well. How would you add a [Month Name] and [Month-Year] and any numerical coulumn to sort the [Month Name] coumn in a power bi report? 

Your help is much appreciated.

Many thanks again👏👍👌

[Anonymous]

Hi @ToussaintSenin ,

I'm sorry I didn't reply in time, it seems I didn't get a prompt for your reply, I hope my reply could still help you to solve your confusion

Please follow the steps below.

Add new columns to create the Month-Name, Month-Year and Month-number.

Month-name = FORMAT([Date],"MMMM")

Month-Year = FORMAT([Date],"YYYY")

Month-number = MONTH('Date'[Date])

Select the [Month Name] column, then in the ‘Column tools’ tab, select ‘Sort by Column’ and select the [MonthNumber] column you just created.

Best regards,

Lucy Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75 · Accepted solution]

I think you might find https://www.sqlbi.com/articles/using-weekly-calendars-in-power-bi/ useful.