Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
BI_Samurai
Frequent Visitor

Default values for data before first available date in fact table

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.

 

BI_Samurai_0-1729238619189.png

 

1 ACCEPTED 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

 

View solution in original post

3 REPLIES 3
BI_Samurai
Frequent Visitor

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

 

FreemanZ
Super User
Super User

Hi @BI_Samurai ,

 

try to plot with a measure like below instead:

 

 

Measure =
IF(
    SUM([Total Sales]) = BLANK(),
    100
)

 

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.