Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Excluding Weekends - Returning data from Friday on Monday otherwise previous day data

Hello!

 

I'm trying to run a calculation that returns data for the previous day but on Monday, I need to return Friday's values.

 

I know that the PREVIOUSDAY function doesn't work and think that I have things narrowed down but am off on the syntax somewhere.

 

Trying to use IF, CALCULATE, DATEADD, and TODAY

I think I need to mix in the WEEKDAY function as well to show if today is Monday (or 1)

 

I'm trying to essentially say that IF (today) is a monday then I want to return the values from my CALCULATE function for Friday using the DATEADD function to go back 2 days otherwise just return the previous day. I'm trying to identify where to put the today equals function as well as my date table.

 

I have my calendar table with weekdays or weekday numbers.

 

Previous Day TOTAL =
IF(TODAY() = 1,
CALCULATE(GL_DETAILPOSTING[TOTAL], DATEADD('Calendar'[Date], -2, DAY)), CALCULATE(GL_DETAILPOSTING[TOTAL],'Calendar'[Date] = TODAY()
))

 

Help please!!

General Comment
Need Help
Show and Tell
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Column:

    Weekday = WEEKDAY([Date],2)

    Measure:

    Measure = 
IF (
    SELECTEDVALUE ( 'Table'[Weekday] ) IN { 6, 7 },
    BLANK (),
    IF (
        SELECTEDVALUE ( 'Table'[Weekday] ) = 1,
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( ALL ( 'Table' ), [Date] = ( SELECTEDVALUE ( 'Table'[Date] ) - 3 ) )
        ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( ALL ( 'Table' ), [Date] = ( SELECTEDVALUE ( 'Table'[Date] ) - 1 ) )
        )
    )
)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Column:

    Weekday = WEEKDAY([Date],2)

    Measure:

    Measure = 
IF (
    SELECTEDVALUE ( 'Table'[Weekday] ) IN { 6, 7 },
    BLANK (),
    IF (
        SELECTEDVALUE ( 'Table'[Weekday] ) = 1,
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( ALL ( 'Table' ), [Date] = ( SELECTEDVALUE ( 'Table'[Date] ) - 3 ) )
        ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( ALL ( 'Table' ), [Date] = ( SELECTEDVALUE ( 'Table'[Date] ) - 1 ) )
        )
    )
)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.