Custom table by today yesterday and day before yesterday and their status by colouring System

[user_34]

Hi Everyone

I have a date picker in Power BI and I want to display the sales of each product on the selected day
plus the day before the selected date and the day before yesterday as shown in the image. and each row should display the target of sales met of that product on specific days in colored bullets that is green specifies that my target on a specific date was met and red specifies it was not met and I want to make dates as a header.

DAX is alreday done for yesterday and day before yesterday.

[Bmejia · Accepted solution]

Here is how I would do it. you can probably create  a measure with variables to join the two measures. I just like doing each step individually.

First create three measure that are going to be link to your date picker.

TodayTotal = CALCULATE(SUM(Sales[TotalSold]),Sales[SaleDate]=MAX(Sales[SaleDate]))

YesterdayTotal = CALCULATE(SUM(Sales[TotalSold]),Sales[SaleDate]=Max(Sales[SaleDate])-1)

Before YesterdayTotal = CALCULATE(SUM(Sales[TotalSold]),Sales[SaleDate]=Max(Sales[SaleDate])-2)

The output should give you something like this if you put it in a table:

Second create another measure that is going to tell you if the total sales meet the target for the day. If value is blank then it be blank, if value meet target 1, if didn't meet target 0. I added a separate table with a list of my target for each product and join the table base on my main table product. (Manual table Target.[Target])

Meet Target Today =
If([TodayTotal]=BLANK(),BLANK(), 

If([Today]>=CALCULATE(SELECTEDVALUE(Target[Target])),1,0))

Meet Target Yesterday =
If([YesterdayTotal]=BLANK(),BLANK(),
If([Yesterday]>=CALCULATE(SELECTEDVALUE(Target[Target])),1,0))

Meet Target Before Yesterday =
If([Before YesterdayTotal]=BLANK(),BLANK(),
If([Before YesterdayTotal]>=CALCULATE(SELECTEDVALUE(Target[Target])),1,0))

--if blank can be excluded if you don't care when a value is blank to say not meet target.

Note,When you put on the table you can rename the measures "Meet Target.." to Today, Yesterday, etc.. or just rename them initially as what they suppose to be. I renamed them in this view.

The output should give you something like this if you put it in a table:

Third you want to change the 0,1 and blanks into icon using the conditional formating by right clicking of each of the met target measures and use this values

Output:

Add your date dropdown picker

As for the dates in your column header there are ways to do it dynamically. I have not done it but if necessary you can just blend the date with the header or do some additional research. I
Create three cards with these measures and use same font as your header play around with it so they fit within your header like i did below. I did disable the backgroud so is not overlapping with the font from chart.

TodayDate = MAX(Sales[SaleDate])
YesterdayDate = MAX(Sales[SaleDate])-1
BeforeYesterdayDate = MAX(Sales[SaleDate])-2

Final Output when selecting today 4/2/2024

Selecting 3/31/2024 with not data enter after this date

[user_34]

Hi, attached the dummy data my main concern is whenever I select date from date picker it should dynamically change the date in the table which will be today date, yesterday date and day before yesterday date

product.xlsx

[v-shex-msft]

Hi @user_34 ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng