Adding an IF statement into Calculate and VAR measure

[abloor]

Hi there,

I'm trying to write a measure that gives me the revenue figures for 7 days prior to the latest 'Snap Date'.  Below is what I have done, which works to give me what I need.

The trouble is, sometimes if on a public holiday, we won't have any revenue figures for 7 days prior, so if blank,  I want Power BI to show me the revenue figures for 6 days prior.  And if blank, 5 days prior.

How would I alter my DAX to incorporate this with an IF statement?

Thank you

Revenue Same Day Last Week =

VAR LatestSnapDate = LOOKUPVALUE ('Table'[SnapDate], 'Table'[SnapDate], MAX('Table'[SnapDate]) )

RETURN

CALCULATE(SUM('Table'[Revenue]),FILTER(ALL('Table'[SnapDate]), 'Table'[SnapDate] = MAX('Table'[SnapDate])-7))

[Anonymous]

Hi @abloor ,

Since there must be two holidays in the last 7 days, I think your requirement could be simply understood as

1.Calculate the sum of the income of the working days in the last 7 days

2.Calculate the total income of the previous 5 days

forSlicer =
CALENDAR ( "2020/8/1", "2020/8/31" )

dateDiff =
DATEDIFF (
    SELECTEDVALUE ( DateRevenue[SnapDate] ),
    SELECTEDVALUE ( forSlicer[Date] ),
    DAY
)

previous5Days =
CALCULATE (
    SUM ( DateRevenue[Revenue] ),
    FILTER ( ALL ( DateRevenue ), [dateDiff] >= 0 && [dateDiff] <= 4 )
)

workingDays =
CALCULATE (
    SUM ( DateRevenue[Revenue] ),
    FILTER (
        ALL ( DateRevenue ),
        [dateDiff] >= 0
            && [dateDiff] <= 6
            && NOT ( ISBLANK ( DateRevenue[Revenue] ) )
    )
)

My visualization looks like this:

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[abloor]

Thank you @amitchandak . It keeps giving me the latest data, not the data from 7 days before.  I'll keep playing around with it and see how I go.

Thank you also @Anonymous  I will give this a try ASAP in the next week.

[Anonymous]

Hi @abloor ,

You are welcome. If I asked your question,please mark my reply as solution.If not ,please upload more detailed samples and expected output. Thank you~

Best regards,

Eyelyn Qin

[abloor]

Thank you for checking in @Anonymous  and for the solution you put forward.  It's not quite what I'm after, so I will find the time to re-word my post and put up an example as requested.

[amitchandak]

@abloor , Try like

measure =
VAR LatestSnapDate = minx(filter( 'Table'[SnapDate] >= MAX('Table'[SnapDate])-7 && not(isblank('Table'[SnapDate]))),'Table'[SnapDate])

RETURN
CALCULATE(SUM('Table'[Revenue]),FILTER(ALL('Table'[SnapDate]), 'Table'[SnapDate] = MAX('Table'[SnapDate])))

[abloor]

@amitchandakthanks for your quick response.

I gave this a go, and it's returning the revenue from the most recent snap date. Not the snap date 7 days prior.

Any ideas how we could change it so it shows 7, then if blank, 6 days, and if blank, 5 days before the snap date?

Many thanks!

[amitchandak]

@abloor , The idea to use get the min snap date >= snap date -7

minx(filter( 'Table'[SnapDate] >= MAX('Table'[SnapDate])-7 && not(isblank('Table'[SnapDate]))),'Table'[SnapDate])

try like

measure =

minx(filter( 'Table',  'Table'[SnapDate] >= today() -7 && not(isblank('Table'[SnapDate]))),'Table'[SnapDate])

RETURN
CALCULATE(SUM('Table'[Revenue]),FILTER(ALL('Table'[SnapDate]), 'Table'[SnapDate] = MAX('Table'[SnapDate])))

or

measure =
var _maxsnap = maxx( allselected('Table'),'Table'[SnapDate])
VAR LatestSnapDate = minx(filter('Table', 'Table'[SnapDate] >= _maxsnap-7 && not(isblank('Table'[SnapDate]))),'Table'[SnapDate])

RETURN
CALCULATE(SUM('Table'[Revenue]),FILTER(ALL('Table'[SnapDate]), 'Table'[SnapDate] = MAX('Table'[SnapDate])))