Using date functions on a table with a single row

[Megz]

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.

• Today = TODAY() - works
• Current month = MONTH('Date Variables'[Today]) - works

• First of Year = DATE(YEAR('Date variables'[Today]),01,01) - works - thanks to @Greg_Deckler articles for steering me away from STARTOFYEAR
• Then I moved on to DATEADD to see what to use to add months and, well, can't do it.

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.

[Megz]

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

[Sergii24]

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:

• full financial year where today's date is a part of
• the next financial year to today's date

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!

[Sergii24]

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.

[Megz · Accepted 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.

[Sergii24]

That why this community exists: to help each of us to get the right direction 🙂
Have a great day!