Need help with Dax for a Custom Month

[EmaVasileva]

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.

[visheshjain]

Hi @EmaVasileva ,

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

[EmaVasileva]

Hi @visheshjain ,

I'm not able to directly attach the files. Can you download them from here: https://drive.google.com/drive/folders/1jNcu5uDNK2RBheHWubGLsdm4EO1vk6BM?usp=sharing

Please let me know if there is an issue. Thank you.

[visheshjain]

Hi @EmaVasileva,

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

[EmaVasileva]

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 · Accepted solution]

Hi @EmaVasileva ,

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