Show sales of same weekday if the measure is blank

[hilary_tesillo]

Hi.

I have this table with the Sales for every day in the month, and I have a second measur called "Sales LM DWY" that corresponds to the last month sales for the same weekday in the same week (1 april was the first friday of the month in the first week so in Sales LM DWY shows the sales of March 4 (that was the first friday of march in the first week of the month)).

I have the problem that for the last two days of April it doesn´t show any value because it doesn´t find the matching weekday and week, so I would like to show instead of blanks the first matching weekday in the same month (For April 29 it should show the sales of April 1st and for April 30 it should show the sales of April 2nd).

I'm struggling to find a solution to do this, I'd appreciate any help. 

[Whitewater100]

Hi:

Do you want to try this measure?

Test =

var currdate = MAX(Dates[Date])

var sales401 = CALCULATE([Sales], Dates[Date] = DATE(2022,4,1))

var sales402 = CALCULATE([Sales], Dates[Date] = DATE(2022,4,2))

return

IF(currdate = DATE(2022,4,29), sales401,

IF(currdate = DATE(2022,4,30), sales402,

[Sales LM DWY]))

[amitchandak · Accepted solution]

@hilary_tesillo , This is usually 28 days behind sales

example with date table

Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-28,Day))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[HenriqueReis]

Hi,

Oh, I think it's not possible, because it's a function, but you can substitute the blank to a character or a word if it helps.

The structure is like:

if[values] = blank; "no match"

In this case, when the value equals blank, instead nothing we gonna have "no match"

Regards!