cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hitesh1607
Helper I
Helper I

DAX for two Date Keys in one table

Hi - I do have a Power BI model with ShipDateKey (Active) and OrderDateKey (Inactive) for fct Sales joined with DimCalendar. I am looking for a DAX calculation that gives me SUM of GrossPrice where ShipDateKey is Current Month ( Jan 2021) AND Order Date is (Dec 2020).

 

I also have DimCalendar with Column RelativeMonth (-1,0,1) as 0 Previous Month and 1 as Current Month.

 

The SQL for the calculation should be like this. 

SELECT SUM([Gross Price]) GP
FROM [DW].[dbo].[v_fctSales]
WHERE OrderDateKey LIKE '202012__' AND ShipDateKey LIKE '202101__'

1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

@hitesh1607 

Measure =
VAR Y1_ =    YEAR ( TODAY () )
VAR M1_ =    MONTH ( TODAY () )
VAR Y0_ =    YEAR ( EDATE ( TODAY (), -1 ) )
VAR M0_ =    MONTH ( EDATE ( TODAY (), -1 ) )
RETURN
    CALCULATE (
        SUM ( Sales[Gross Price] ),
        YEAR ( Sales[ShipDateKey] ) = Y1_,
        MONTH ( Sales[ShipDateKey] ) = M1_,
        YEAR ( Sales[OrderDate] ) = Y0_,
        MONTH ( Sales[OrderDate] ) = M0_
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

View solution in original post

3 REPLIES 3
AlB
Super User III
Super User III

@hitesh1607 

Measure =
VAR Y1_ =    YEAR ( TODAY () )
VAR M1_ =    MONTH ( TODAY () )
VAR Y0_ =    YEAR ( EDATE ( TODAY (), -1 ) )
VAR M0_ =    MONTH ( EDATE ( TODAY (), -1 ) )
RETURN
    CALCULATE (
        SUM ( Sales[Gross Price] ),
        YEAR ( Sales[ShipDateKey] ) = Y1_,
        MONTH ( Sales[ShipDateKey] ) = M1_,
        YEAR ( Sales[OrderDate] ) = Y0_,
        MONTH ( Sales[OrderDate] ) = M0_
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

View solution in original post

AlB
Super User III
Super User III

Hi @hitesh1607 

Measure =
CALCULATE (
    SUM ( Sales[Gross Price] ),
    YEAR ( Sales[ShipDateKey] ) = 2021,
    MONTH ( Sales[ShipDateKey] ) = 1,
    YEAR ( Sales[OrderDate] ) = 2020,
    MONTH ( Sales[OrderDate] ) = 12
)

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Hi @AlB - This way my DAX will become static and I need a dynamic solution that can determine the Current Month for the Ship date and the previous month for the Order Date. Is there a way we can write a DAX using relative Calendar month ?? I Do have that in my dimCalendar and it changes dynamically.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors