Forum Discussion

ToussaintSenin's avatar
ToussaintSenin
Regular Visitor
1 year ago
Solved

DAX Measure

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 ...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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 1st Apr of previous year. If it is greater than or equal to 4, it is 1st April of the current year.

    2. Financial week starts on the 1st 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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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 1st Apr of previous year. If it is greater than or equal to 4, it is 1st April of the current year.

2. Financial week starts on the 1st 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's avatar
    ToussaintSenin
    Regular Visitor
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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.