Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |