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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
WHH
Frequent Visitor

Different Evaluation in DAX Studio vs Desktop

Hello,

I have a calendar which has a True/False column for Previous Month.

I have a report builder who wants to show a line chart of activity for previous month - with values for the last three years as below:

WHH_0-1620214300987.png

 

Currently they have to go in each month and change the page level filter, so I want a dynamic solution.

The approach I've tried has been to add a column to the calendar (using DAX as not sure how to begin this with M) - and have the new column 1) find the value for previous month, 2) returns this as a single / scalar value, 3) and use this scalar to compare to the month in the row context.

 

I have tried created two different approaches, and the frustrating thing is they work when I test it in DAX studio with ADDCOLUMNS(), but when I try to get it to work in Desktop with New Column, the evaulation context seems to change and it doesn't work.

 

Here is the DAX studio code, I re-write it for Desktop accordingly (i.e. don't use EVALUATE or ADDCOLUMNS in these statements - I'll post these if it's helpful, I didn't for reasons of brevity)

 

Attempt 1

Prev Month YoY =

VAR PrevMonthName =
    CALCULATETABLE (
        VALUES ( 'Calendar'[Month Name] ),
        'Calendar'[Previous Month] = TRUE ()
    )
RETURN
IF ( EXACT ( 'Calendar'[Month Name], PrevMonthName ), TRUE (), FALSE () )

 

Attempt 2

 

EVALUATE
VAR PrevMonth =
    MONTH (
        TOPN (
            1,
            CALCULATETABLE (
                PREVIOUSMONTH ( 'Calendar'[Date] ),
                'Calendar'[Date] = TODAY ()
            )
        )
    )
RETURN
    ADDCOLUMNS (
        'Calendar',
        "PrevMonthPrevPeriod",
            IF ( 'Calendar'[Month] - PrevMonth = 0, TRUE (), FALSE () )
    )

 

 

The errow seems to be caused by the evaluation of the Variable. I didn't think this would be impacted by the row context but seemingly it is?! i.e. Attempt 2 returns one "TRUE" result - which is in the row for today's date. i.e. it seems to evaluate the VAR and only work in the row where TODAY() is true... 

 

I didn't know that evaluation context worked like that, or would be different between DAX Studio (where it works as expected) and Desktop.

 

I don't mind how I approach the solution to this, my attempts above have been unsuccessful, so any suggestions welcome, i.e. if I can somehow do this in M and add a column that way it could be best solution but I wasn't sure how to do this. 

Any ideas would be gratefully received.

If I haven't made it clear feel free to ask follow up questions.

2 REPLIES 2
amitchandak
Super User
Super User

@WHH , one-way use today for time intelligence , I explained in this video

All About Time Intelligence around Today: https://youtu.be/gcLhhxhXKEI

 

The second is the default date on this month using a column like in date table

Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY")
)

 

 

Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=35

 

use measures like this with a default date

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Thank you for taking the time to read my question and respond, and thank you also for your informative video links.

I found they didn't quite provide me with the information needed to resolve this. I think this may be a fault of the way I worded my question.

I have a column in my calendar which evaluates to True if a date was last month. If last month was "April" it will return True against all dates in April this year.

I now need to create another column which returns True for every April date regardless of year.

The DAX I wrote above returned this value when I ran it in DAX Studio. However when running the code in Power BI Desktop it didn't work. Seemingly the variable was evaluated differently in Desktop than in Studio.

Any ideas how I can create a column which will return True regardless of year - and will update to reflect the 'previous month' based on today's date (i.e. it will mark April as True when we're in May, and when we're in July all dates in May will be marked as True).

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.