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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cristianj
Helper IV
Helper IV

What am i doing wrong

Hello

i have this messure, the ideea is to show value from 7 days back, if i select a date, from that date , if nothing is selected from today - 7

so this is tha data which is working for the today , or any other selection

Selected Day -7 =
var tempdate=
calculate(
    sum('Orders Sent last 31 days'[Valoare Comenzi Plecate de la Flip (EUR)]
        )
    ,filter('Orders Sent last 31 days'
        ,'Orders Sent last 31 days'[data]=  
            if(
                ISFILTERED('Date'[DateDispaly])=TRUE()
                ,CALCULATE(max('Orders Sent last 31 days'[data]),ALLSELECTED('Orders Sent last 31 days'[data]))
                ,today()
            )
            )
)
return tempdate
Problem is that when i chage it to 
Selected Day -7 =
var tempdate=
calculate(
    sum('Orders Sent last 31 days'[Valoare Comenzi Plecate de la Flip (EUR)]
        )
    ,filter('Orders Sent last 31 days'
        ,'Orders Sent last 31 days'[data]=  
            if(
                ISFILTERED('Date'[DateDispaly])=TRUE()
                ,CALCULATE(max('Orders Sent last 31 days'[data]),ALLSELECTED('Orders Sent last 31 days'[data]))
                ,today()
            )-7, /* this is the only change */
            )
)
return tempdate
it display blank
i presum is had to do with column type, the strange thing is, if i display only date value is working
Thank you for your time.
1 ACCEPTED SOLUTION
Sergii24
Super User
Super User

Hi @cristianj, your calculation looks to be overcomplicated... Is there a spicific reason why you wrote it in this way? You can achieve the similar result much simplier:

 

Sales 7 days before = 
VAR _CurrentDate = SELECTEDVALUE( CalendarTable[Date] )             //we need to remember the currently selected date, because we0ll overwrite it with Calculate() later
RETURN
    CALCULATE(
        SUM( Sales[Sales] ),
        CalendarTable[Date] = _CurrentDate - 7
    )

 

 

Sergii24_0-1693302853763.png

Let me know if it works or if I misunderstood your question.

View solution in original post

6 REPLIES 6
Sergii24
Super User
Super User

Hi @cristianj, your calculation looks to be overcomplicated... Is there a spicific reason why you wrote it in this way? You can achieve the similar result much simplier:

 

Sales 7 days before = 
VAR _CurrentDate = SELECTEDVALUE( CalendarTable[Date] )             //we need to remember the currently selected date, because we0ll overwrite it with Calculate() later
RETURN
    CALCULATE(
        SUM( Sales[Sales] ),
        CalendarTable[Date] = _CurrentDate - 7
    )

 

 

Sergii24_0-1693302853763.png

Let me know if it works or if I misunderstood your question.

Thank you werry much, with your help i change the formula and now it is working

Selected Day -7 Beta =
var currentDate=if(ISFILTERED('Date'[DateDispaly])=TRUE(),SELECTEDVALUE('Date'[Date]),TODAY())
return
    calculate(
            sum('Orders Sent last 31 days'[Valoare Comenzi Plecate de la Flip (EUR)]),
            'Date'[Date]=currentDate-7)
 
Do you have any ideea what thas not working before?
Thank you again
 

I believe there is something related to Context Transition - Understanding context transition in DAX - SQLBI. Unfortunately I can't dedicate more time to debug your code but you can do so by yourself after carefully reading this article 🙂

Thank you i will change and try, 

i write it in such way, because if is nothing selected, it has to use the today as a starting point, but i try and let you know

 

I see, here is the updated code (read comment for more details on it):

Sales 7 days before = 
VAR _CurrentDate = 
    SELECTEDVALUE( 
        CalendarTable[Date], 
            MAXX( ALL( CalendarTable[Date] ), 
            TODAY() 
        )
    )
//we need to remember the currently selected date, because we'll overwrite it with Calculate() later. SELECTEDVALUE() return only 1 value, so if multiple dates are selected, we need to return an alternative result - the second argument of the formula. In this case I used Today() to obtain today's date, but you can replace it with any other date, for instance absolute MAX of CalendarTable. Simply replace the code of variable from the above with the following:
/*
VAR _CurrentDate = 
    SELECTEDVALUE( 
        CalendarTable[Date], 
        MAXX( 
            ALL( CalendarTable[Date] ), 
            CalendarTable[Date] 
        ) 
    )
*/
RETURN
    CALCULATE(
        SUM( Sales[Sales] ),
        CalendarTable[Date] = _CurrentDate - 7
    )

and i try to make it a messure, because i need to display the current value(today or the selected one), current value 7 days back and current value 14 days back

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors