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
berta2b
Helper I
Helper I

DAX Functions for various time periods

Hi there guys, I'm very new toDAX functions and having problems trying to find ones specific for my needs.

 

I have a Matrix Visualisation: [Rows] As ID's and a collection of [Values] as different Calculated Columns containing SUM() and Calculated Values. There are NO columns so effectively I'm grouping these Calculated columns By ID's

 

What I'm trying to do is report on each Value by a Financial Period, now for YTD thats fine as I use TotalYTD

i.e. YTDOrdersCount = TOTALYTD(SUM(Orders[OrdersCount]), Orders[OrderDate])

 

But what I'd like are values using DAX functions for:

PREVIOUS WEEK FROM TODAY

2 WEEKS PREVIOUS FROM TODAY

PREVIOUS MONTH (CURRENT YEAR) - Feb 17

CURRENT MONTH (CURRENT YEAR) - Mar 17

PREVIOUS MONTH (LAST YEAR) - Feb 16

CURRENT MONTH (LAST YEAR) - Mar 16

 

Here is an example of report

 

ID     SumOfOrders (Previous Week)   SumOfOrders (Current Month) etc ...

--      -------------------------------  ---------------------------------

123                       20                                                     200

 

I hope I have explained this okay.

 

The normal solution would be write DB SQL and calculate these values GROUPED BY ID's but ideally I'd like to use DAX Functions to achieve this.

 

Please can I have some help with building these functions.

 

Many Thanks guys for your help.

 

1 ACCEPTED SOLUTION

Couple of things.  To the OP:

 

For the time intelligence functions to work properly, you need a separate, best practice calendar table with full year, contiguous dates for all the years you need to report against.  Then relate to fact tables via the Date field.

 

Using DATEADD is great, but you don't (and maybe shouldn't) need to use FILTER with them:

 

Prior Month =
CALCULATE ([SumOfOrders], DATEADD ( DateTable[Date], -1, MONTH ) )
)

 

Prior Year=
CALCULATE ( [SumOfOrders], SAMEPERIODLASTYEAR ( DateTable[Date] ) )
)  

and so on.   There are differences in how the functions calculate and shift the visible dates, so good to read up on the reference pages so you understand how they work.

View solution in original post

4 REPLIES 4

Hi there,

 

Have you had a chance to utilize the DATEADD Function. The name is slightly misleading since it has ADD in it. But you can either ADD or SUBTRACT using that function.

 

 

=
DATEADD(DateTable[Date], -1, MONTH )

 

So an example of that wrapped in a CALCULATE formula could be:

=
CALCULATE (
    [SumOfOrders],
    FILTER ( DateTable, DATEADD ( DateTable[Date], -1, MONTH ) )
)

The numberical value in DATEADD can be both +/-and the intervals can be DAY, MONTH, YEAR, etc... The last thing you can also use is the various PREVIOUS functions in DAX (E.g. PREVIOUSYEAR, PREVIOUSMONTH, etc...). So as example if you wanted to calculate [SumOfOrders] for the Previous Month of Last Year you could use something like below.

 

=
CALCULATE (
    [SumOfOrders],
    FILTER ( DateTable, PREVIOUSYEAR ( DATEADD ( DateTable[Date], -1, MONTH ) ) )
)

Hopefully some of these are helpful or point you in the right direction.

 

Reid_Havens,

 

Thanks so much for your response, and yes I'm strong in SQL so very familiary with DATEADD.

 

You have defo pointed me in the right direction, and the examples you supplied are very useful.

 

Many Thanks again

Couple of things.  To the OP:

 

For the time intelligence functions to work properly, you need a separate, best practice calendar table with full year, contiguous dates for all the years you need to report against.  Then relate to fact tables via the Date field.

 

Using DATEADD is great, but you don't (and maybe shouldn't) need to use FILTER with them:

 

Prior Month =
CALCULATE ([SumOfOrders], DATEADD ( DateTable[Date], -1, MONTH ) )
)

 

Prior Year=
CALCULATE ( [SumOfOrders], SAMEPERIODLASTYEAR ( DateTable[Date] ) )
)  

and so on.   There are differences in how the functions calculate and shift the visible dates, so good to read up on the reference pages so you understand how they work.

And thanks mattbrice, I'll try not using FILTER to achieve this.

 

Many Thanks again for your suggestions.

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.