Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need help with Dax for a Custom Month

Hi all,

I’m using a custom week which starts from Thursday and ends on Wednesday. (first week in 2022 starts from January 6th). I need a Dax calculation/measure for creating a CustomMonth. If my CustomWeek starts in one month and ends in the next, I would like my CustomMonth to show the month of the Customweek start date. If the CustomWeek starts and ends in the same month- the month stays the original one. For example:



I tried to use the formula suggested by @az38: (https://community.powerbi.com/t5/Desktop/two-months-in-one-week/m-p/532525)

MONTH(DATE(YEAR(CalendarDate[Date])-1,12,31)+(CalendarDate[CustomWeek]) * 7)

It works for almost all dates, except the ones that the last day of the month is Thursday and the new custom weeks starts( for ex. March31 and June30).

Also the tricky part is that I’m trying to use this calculation/measure as a hidden filter applied to a clustered column chart. If I select from a Slicer 'CustomWeek 5', I would like to see in my chart the CustomMonth for this week.

Here I attached a sample of my data in excel and also a PBI file: https://files.fm/u/fjhu4kjuv

I will appreciate any help.

Need Help
Show and Tell
  • visheshjain's avatar
    visheshjain
    4 years ago

    Hi Anonymous ,

     

    Please define the following columns in your calendar table and 'Mark as date table'.

     

    Year = YEAR(CalendarDate[Date])
     
    Custom Year = IF(
    CalendarDate[CustomWeek]>=52 &&
    CalendarDate[MonthNumber]=1,
    CalendarDate[Year]-1,
    CalendarDate[Year]
    )
     
    YearWeek Key = CalendarDate[Custom Year]*100 + CalendarDate[CustomWeek]
     
    Year Month Key = CalendarDate[Year]*100+ CalendarDate[MonthNumber]
     
    Custom Month = CALCULATE(min(CalendarDate[Year Month Key]), ALLEXCEPT(CalendarDate, CalendarDate[YearWeek Key])) - CalendarDate[Custom Year]*100
     
    We might be able to get Custom Month by defining a single column using variables, but I suggest that you create these column so that they can be used in your other measures as well.
     
    Hope this solves your problem and if it does please accept is a solution and kudo it, so that others can reach the solution faster.
     
    Thank you,
    Vishesh Jain

5 Replies

  • visheshjain's avatar
    visheshjain
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    Hi Anonymous ,

    I am unable to download your file, please can you share the file as an attachment here, I'd like to take a dig at it.

    I think you can make a YearWeek key and get the minimum month for that YearWeek key.

    I'll have to check how to write the code in DAX, so please if you could somehow share the file.

     

    Thank you,

    Vishesh Jain

      • visheshjain's avatar
        visheshjain
        Icon for Impactful Individual rankImpactful Individual
        4 years ago

        Hi Anonymous,

         

        Please can you have a look at the following screenshot and see if this is what you are looking for.

        I have used calculated columns instead of measures, as I think all these calculations should be a part of your calendar table rather than just a single measure. This will also enable you to use these calculations/logic in defining other measures as well.

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        If it solved your problem, please can you provide me write permission to the Google Drive folder to share the file with you.

        If not then please provide further information.

        Hope this helps solves your problem and if it does, then please accept it as a solution and kudo it as well, so that other can reach the solution faster.

         

        Thank you,

        Vishesh Jain