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 C...
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
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi visheshjain,

Thank you for the quick test. From the screenshot everything looks okey. Can you please tell me in Custom month for February 1st and March 31st what are is the number?
Also, can you send me or PM me your email, to give you permissions to Google Drive?

visheshjain's avatar
visheshjain
Icon for Impactful Individual rankImpactful Individual
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