Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to Solution.
Hi @EmaVasileva ,
Please define the following columns in your calendar table and 'Mark as date table'.
Proud to be a Super User!
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
Proud to be a Super User!
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.
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
Proud to be a Super User!
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?
Hi @EmaVasileva ,
Please define the following columns in your calendar table and 'Mark as date table'.
Proud to be a Super User!
User | Count |
---|---|
85 | |
74 | |
71 | |
68 | |
56 |
User | Count |
---|---|
98 | |
96 | |
92 | |
78 | |
70 |