Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I have a small chalenge with past dates and values.
In my facts table first sales values start in January 2024. I have actual sales data from Jan till Dec 2024.
In my calendar table and on visuals I will be also displaying data for 2023 although no actuals sales occured in this period and data in facts table is not available.
Is there a way for me to populate with DAX all dates in the past before first sales occured (<Jan 2024) as a default 100 for example?
Thank you for help.
Solved! Go to Solution.
hi @BI_Samurai ,
try this and what is attached:
Measure =
VAR _maxsalesdate = MAXX(ALLSELECTED(data), data[Date])
VAR _minsalesdate = MINX(ALLSELECTED(data), data[Date])
VAR _currentdate = MAX(dates[Date])
VAR _result =
SWITCH(
TRUE(),
_minsalesdate>_currentdate, 100,
_maxsalesdate<_currentdate, 1000,
SUM(data[Sales])
)
RETURN _result
hello, thanks for you reply.
My case is more complex so I would like to define in DAX a rule related to the dates in the past before sales occured (maybe something with max/min dates) because I need to apply a different rule for blanks in the future. That's why I need to distinguish those 2 cases and I cannot just put blanks everywhere. I need DAX to be more specific to the dates in the past. Thank you.
hi @BI_Samurai ,
try this and what is attached:
Measure =
VAR _maxsalesdate = MAXX(ALLSELECTED(data), data[Date])
VAR _minsalesdate = MINX(ALLSELECTED(data), data[Date])
VAR _currentdate = MAX(dates[Date])
VAR _result =
SWITCH(
TRUE(),
_minsalesdate>_currentdate, 100,
_maxsalesdate<_currentdate, 1000,
SUM(data[Sales])
)
RETURN _result
Hi @BI_Samurai ,
try to plot with a measure like below instead:
Measure =
IF(
SUM([Total Sales]) = BLANK(),
100
)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |