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 There,
I am trying to set up some date variables which are relative to today's date. I have an SQL script that was previously used to create these variables, but now I want them in DAX.
example of the sql
CASE WHEN MONTH(NOW())>=10 THEN MAKEDATE(YEAR(NOW()),1) + interval 9 month
WHEN MONTH(NOW())<10 THEN MAKEDATE(YEAR(NOW()-interval 1 year),1) + interval 9 month end as first_day_oct_this_fy,
and so on for first days and last days of month for this and next fy (financial year)
Our financial year runs Oct - Oct
So I started setting up a table and breaking down the SQL into bite size to see which functions to use.
I have read @Greg_Deckler posts on to bleep with DATEADD
https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/To-bleep-With-DATEADD/td-p/1259467
But can't seem to make it work for a table with a single column
I need to be able to compare the current month to October, then depending on that either add 9 months to the first of the current year or the first of last year.
Any pointers very greatly appreciated.
Solved! Go to Solution.
Thanks so much @Sergii24.
You've set me off on the right track. I adapted what you sent me to get what I needed eg
first_day_oct_this_fy =
VAR _Today = TODAY() //obtain today's date
VAR _MonthOfToday = MONTH( _Today ) //obtain the month of today
VAR _YearOfToday = YEAR( _Today ) //obtain the calendar year of today
VAR _FinacialYearOfToday = //define the financial year for this date
IF(
_MonthOfToday <= 10,
_YearOfToday - 1, //the date is a part of financial year that started in the previous calendar year
_YearOfToday //the date is the part of finacial year that started in the current calendar year
)
VAR _FirstDayOctThisFY = DATE( _FinacialYearOfToday, 10, 1 ) //calculate the min date for the calendar
RETURN (_FirstDayOctThisFY)
And ....
last_day_oct_this_fy =
VAR _Today = TODAY() //obtain today's date
VAR _MonthOfToday = MONTH( _Today ) //obtain the month of today
VAR _YearOfToday = YEAR( _Today ) //obtain the calendar year of today
VAR _FinacialYearOfToday = //define the financial year for this date
IF(
_MonthOfToday <= 10,
_YearOfToday - 1, //the date is a part of financial year that started in the previous calendar year
_YearOfToday //the date is the part of finacial year that started in the current calendar year
)
VAR _FirstDayOctThisFY = DATE( _FinacialYearOfToday, 10, 1 )
VAR _LastDayOctThisFY = EOMONTH(_FirstDayOctThisFY,0)
RETURN _LastDayOctThisFY
etc for the other months.
I can't thank you enough for pointing me in the right direction.
Thanks for reading @Sergii24
Let me see if I can articulate what I'm after a little better. The table below has the output of the SQL statement based on today's date (26th March).
Here's the SQL that calculates the first and last dates for October then the intervals are incremented to work through the months.
CASE WHEN month(now())>=10 THEN MAKEDATE(year(now()),1) + interval 9 month
WHEN month(now())<10 THEN MAKEDATE(year(now()-interval 1 year),1) + interval 9 month end as first_day_oct_this_fy,
CASE WHEN month(now())>=10 THEN LAST_DAY(MAKEDATE(year(now()),1) + interval 9 month)
WHEN month(now())<10 THEN LAST_DAY(MAKEDATE(year(now()-interval 1 year),1) + interval 9 month) end as last_day_oct_this_fy,
Our financial year runs October to October so based on any date in the year I need dates for the first and last day of each month in this and next financial year.
I'd also like the output to be pivoted so that the variable names in the first column become the column headings to that I can use them in measures for futher calculations.
# variable | value |
first_day_oct_this_fy | 2023-10-01 |
last_day_oct_this_fy | 2023-10-31 |
days_oct_this_fy | 31 |
first_day_nov_this_fy | 2023-11-01 |
last_day_nov_this_fy | 2023-11-30 |
days_nov_this_fy | 30 |
first_day_dec_this_fy | 2023-12-01 |
last_day_dec_this_fy | 2023-12-31 |
days_dec_this_fy | 31 |
first_day_jan_this_fy | 2024-01-01 |
last_day_jan_this_fy | 2024-01-31 |
days_jan_this_fy | 31 |
first_day_feb_this_fy | 2024-02-01 |
last_day_feb_this_fy | 2024-02-29 |
days_feb_this_fy | 29 |
first_day_mar_this_fy | 2024-03-01 |
last_day_mar_this_fy | 2024-03-31 |
days_mar_this_fy | 31 |
first_day_apr_this_fy | 2024-04-01 |
last_day_apr_this_fy | 2024-04-30 |
days_apr_this_fy | 30 |
first_day_may_this_fy | 2024-05-01 |
last_day_may_this_fy | 2024-05-31 |
days_may_this_fy | 31 |
first_day_jun_this_fy | 2024-06-01 |
last_day_jun_this_fy | 2024-06-30 |
days_jun_this_fy | 30 |
first_day_jul_this_fy | 2024-07-01 |
last_day_jul_this_fy | 2024-07-31 |
days_jul_this_fy | 31 |
first_day_aug_this_fy | 2024-08-01 |
last_day_aug_this_fy | 2024-08-31 |
days_aug_this_fy | 31 |
first_day_sep_this_fy | 2024-09-01 |
last_day_sep_this_fy | 2024-09-30 |
days_sep_this_fy | 30 |
first_day_oct_next_fy | 2024-10-01 |
last_day_oct_next_fy | 2024-10-31 |
days_oct_next_fy | 31 |
first_day_nov_next_fy | 2024-11-01 |
last_day_nov_next_fy | 2024-11-30 |
days_nov_next_fy | 30 |
first_day_dec_next_fy | 2024-12-01 |
last_day_dec_next_fy | 2024-12-31 |
days_dec_next_fy | 31 |
first_day_jan_next_fy | 2025-01-01 |
last_day_jan_next_fy | 2025-01-31 |
days_jan_next_fy | 31 |
first_day_feb_next_fy | 2025-02-01 |
last_day_feb_next_fy | 2025-02-28 |
days_feb_next_fy | 28 |
first_day_mar_next_fy | 2025-03-01 |
last_day_mar_next_fy | 2025-03-31 |
days_mar_next_fy | 31 |
first_day_apr_next_fy | 2025-04-01 |
last_day_apr_next_fy | 2025-04-30 |
days_apr_next_fy | 30 |
first_day_may_next_fy | 2025-05-01 |
last_day_may_next_fy | 2025-05-31 |
days_may_next_fy | 31 |
first_day_jun_next_fy | 2025-06-01 |
last_day_jun_next_fy | 2025-06-30 |
days_jun_next_fy | 30 |
first_day_jul_next_fy | 2025-07-01 |
last_day_jul_next_fy | 2025-07-31 |
days_jul_next_fy | 31 |
first_day_aug_next_fy | 2025-08-01 |
last_day_aug_next_fy | 2025-08-31 |
days_aug_next_fy | 31 |
first_day_sep_next_fy | 2025-09-01 |
last_day_sep_next_fy | 2025-09-30 |
days_sep_next_fy | 30 |
Hi @Megz, I've went carefully once again through your messages. If I understood you correctly the task is the following: every time when you refresh the report, based on the current date calculate the table that contains dates in 2 financial years (financial year start on October 1st). The years to calcualte are:
Therefore, in the example of 26th of March 2024, the current financial year is Oct 1st 2023 - Sep 30 2024 and the next one is Oct 1st 2024 - Sep 30 2025.
Is it correct?
I suppose you need these dates later in your report to filter data and/or create other calculations. If it's so, I would assume that you need to create a Calendar Table (which you'll mark as Date Table), that later is going to be connected with other tables using date as a key.
The following code will help you to achieve the objective above:
Calendar Table =
VAR _Today = TODAY() //obtain today's date
VAR _MonthOfToday = MONTH( _Today ) //obtain the month of today
VAR _YearOfToday = YEAR( _Today ) //obtain the calendar year of today
VAR _FinacialYearOfToday = //define the financial year for this date
IF(
_MonthOfToday <= 10,
_YearOfToday - 1, //the date is a part of financial year that started in the previous calendar year
_YearOfToday //the date is the part of finacial year that started in the current calendar year
)
VAR _MinDate = DATE( _FinacialYearOfToday, 10, 1 ) //calculate the min date for the calendar
VAR _MaxDate = DATE( _FinacialYearOfToday + 2, 9, 30 ) //calculate the max date for the calendar ( +2 is used to include 2 years: current and the next finacial years)
RETURN CALENDAR( _MinDate, _MaxDate )
This is a DAX code to use in after you click on Home->"New table". You can then add more columns such as start of the month, end of the month, number of dates and so on using "Add column" fucntionality (there are other methods to make it more efficient but it could be easier to tackle it step-by-step).
I hope it helps!
Hi @Megz, could you send us a simple 2-4 rows table with a sample data and the column with the expected output? Make sure we can copy-paste it to help you faster 😉 Current DAX measure code which doesn't work would help as well.
Thanks so much @Sergii24.
You've set me off on the right track. I adapted what you sent me to get what I needed eg
first_day_oct_this_fy =
VAR _Today = TODAY() //obtain today's date
VAR _MonthOfToday = MONTH( _Today ) //obtain the month of today
VAR _YearOfToday = YEAR( _Today ) //obtain the calendar year of today
VAR _FinacialYearOfToday = //define the financial year for this date
IF(
_MonthOfToday <= 10,
_YearOfToday - 1, //the date is a part of financial year that started in the previous calendar year
_YearOfToday //the date is the part of finacial year that started in the current calendar year
)
VAR _FirstDayOctThisFY = DATE( _FinacialYearOfToday, 10, 1 ) //calculate the min date for the calendar
RETURN (_FirstDayOctThisFY)
And ....
last_day_oct_this_fy =
VAR _Today = TODAY() //obtain today's date
VAR _MonthOfToday = MONTH( _Today ) //obtain the month of today
VAR _YearOfToday = YEAR( _Today ) //obtain the calendar year of today
VAR _FinacialYearOfToday = //define the financial year for this date
IF(
_MonthOfToday <= 10,
_YearOfToday - 1, //the date is a part of financial year that started in the previous calendar year
_YearOfToday //the date is the part of finacial year that started in the current calendar year
)
VAR _FirstDayOctThisFY = DATE( _FinacialYearOfToday, 10, 1 )
VAR _LastDayOctThisFY = EOMONTH(_FirstDayOctThisFY,0)
RETURN _LastDayOctThisFY
etc for the other months.
I can't thank you enough for pointing me in the right direction.
That why this community exists: to help each of us to get the right direction 🙂
Have a great day!
User | Count |
---|---|
89 | |
84 | |
65 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |