Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shill1000
Helper IV
Helper IV

Using non-month end relative date

Hope someone has a solution for this. i have a need to report data every month on a financial period, not calendar month. This will be 27th month A to 26th month B.

 

I can obviously hard code the dates into the query, or I can use a secondary table with the dates to be passed as parameters, but ideally I want a query that always runs for the 27th day of Previous Month to 26th day of Current Month.

 

I was figuring that i should be able to try something like:

Date 1 = start previous month +26

Date 2 = start current month +27 (then use less than Date 2 as end of period)

 

I get the logic, I just can't work out the syntax.

1 ACCEPTED SOLUTION

Hi @shill1000,


You can try to use below formula to calculate specific date range who generated by current date.

 

Measure version:

 

Dynamic result =
VAR current_Date =
    MAX ( 'Table'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALL ( 'Table' ),
            [Date]
                >= DATE ( YEAR ( current_Date ), MONTH ( current_Date ) - 1, 27 )
                && [Date] <= DATE ( YEAR ( current_Date ), MONTH ( current_Date ), 26 )
        )
    )

 

Calculate column version:

Dynamic result =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date]
            >= DATE ( YEAR ( EARLIER ( 'Table'[Date] ) ), MONTH ( EARLIER ( 'Table'[Date] ) ) - 1, 27 )
            && 'Table'[Date]
                <= DATE ( YEAR ( EARLIER ( 'Table'[Date] ) ), MONTH ( EARLIER ( 'Table'[Date] ) ), 26 )
    )
)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
vanessafvg
Super User
Super User

@shill1000 i am assuming you have a date table?

 

Another way of doing it is creating

1. FiscalDayofmonth

2 FiscalMonth

3 Fiscal Year columns?

 

and then you can use those fields to filter you info by





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Thanks for the swift response.

 

I haven't needed a date table yet as it's a simple report with no other date handling to warrant a date table.  All I want the query to do is fetch the relative months records with minimum fuss. As it will only ever fetch one months records, and always between these days, I can't help thiniking that inserting fiscal year/month/day columns will be overkill. If it is the only solution then I guess I must do that, but I can't help thinking there has to be a simpler more elegant solution.

@shill1000 ah ok you can always use the datesbetweenfunction i guess?

 

https://msdn.microsoft.com/en-us/library/ee634557.aspx

 

=CALCULATE(SUM(measure), DATESBETWEEN(table[date],  
    DATE(2007,6,1),  
    DATE(2007,8,31)  
  ))  

have to rush off to a meeting but there must be a way to make it dynamic 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Thanks, but I decided to use a date table anyway. I added a column to calculate the financial month a date falls in (if date <27 then month else month+1). I then merged the 2 queries and pulled in the financial month value, then use month value as the filter. It's a bit clunky but it allows the reprot to be automated.

 

Thanks for the pointers.

Hi @shill1000,


You can try to use below formula to calculate specific date range who generated by current date.

 

Measure version:

 

Dynamic result =
VAR current_Date =
    MAX ( 'Table'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALL ( 'Table' ),
            [Date]
                >= DATE ( YEAR ( current_Date ), MONTH ( current_Date ) - 1, 27 )
                && [Date] <= DATE ( YEAR ( current_Date ), MONTH ( current_Date ), 26 )
        )
    )

 

Calculate column version:

Dynamic result =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date]
            >= DATE ( YEAR ( EARLIER ( 'Table'[Date] ) ), MONTH ( EARLIER ( 'Table'[Date] ) ) - 1, 27 )
            && 'Table'[Date]
                <= DATE ( YEAR ( EARLIER ( 'Table'[Date] ) ), MONTH ( EARLIER ( 'Table'[Date] ) ), 26 )
    )
)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks Xiaoxin, I'll try that out and keep it in mind if I need a new solution or to repeat the current exercise. 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.