Build a graph with specific conditions

[game1]

Suppose I have the table below.
I want a graph where on the Y axis, we have the number of Num and on the X axis, the dates in days and months (Table[date]). The graph will count the number of Num (Table[Num]) where the color is different from "black".

Also, there are the following conditions:
1-months and days must only be those of the last 7 days from Today()
2-Also, if a date OF THE LAST 7 DAYS does not exist in the table[date], this date should be displayed, but with the number of Num must be 0. (for example, because 2024-02-18 do not appear in the table[date], but it is part of the last 7 days from today, it must be displayed, and in the Axis Y, the value must be 0.
3-If on a specific date, we just have a color "black", the number of Num must also be 0.

So, for the last 7 days, I want the intervall of 2024-02-13 to 2024-02-19.

[Anonymous · Accepted solution]

Hi @game1 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a date table.

DateTable = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))

(3) We can create measures. 

Measure = 
var _a= COUNTROWS(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date]) && 'Table'[Color] <> "black"))
RETURN IF(_a=BLANK(),0,_a)

Flag = IF(MAX('DateTable'[Date])<=TODAY() && MAX('DateTable'[Date]) >=TODAY()-6,1,0) 

(4) Place [Flag=1] on the visual object screening and then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[123abc]

1. Create a new calculated table or measure that generates the date range for the last 7 days from today.
2. Use DAX to calculate the count of Num where the color is different from "black" for each date in the last 7 days.
3. Handle cases where a date does not exist in the table[date] and where the color is "black".

Here's how you can achieve this:

1. Calculate the Date Range for the Last 7 Days:

Last7Days =
VAR TodayDate = TODAY()
RETURN
FILTER(
CALENDAR(TODAY() - 6, TODAY()),
[Date] <= TodayDate
)

Calculate the Count of Num for Each Date:

NumCount =
VAR Last7Days = CALCULATETABLE(Last7Days, ALL(Table))
RETURN
ADDCOLUMNS(
Last7Days,
"NumCount",
CALCULATE(
COUNTROWS(Table),
Table[color] <> "black",
Table[date] = [Date]
)
)

Handle Cases Where a Date Does Not Exist or Color is "black":

NumCount =
VAR Last7Days = CALCULATETABLE(Last7Days, ALL(Table))
RETURN
ADDCOLUMNS(
Last7Days,
"NumCount",
IF(
ISBLANK(
LOOKUPVALUE(
Table[Num],
Table[date], [Date]
)
),
0,
IF(
LOOKUPVALUE(
Table[color],
Table[date], [Date]
) = "black",
0,
CALCULATE(
COUNTROWS(Table),
Table[color] <> "black",
Table[date] = [Date]
)
)
)
)

Once you have these measures or calculated tables, you can use them to create a line or bar graph where the X-axis is the date and the Y-axis is the NumCount. Make sure to use the NumCount measure as the values for your graph.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[game1]

Hello, 

I have try it, but it is not working verry well.