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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
walkerstorrer
New Member

"Show items with no data" within a date range

I'm trying to show sales quantity over time. Some months there are no sales, but I want those x-axis headers to show for those months. By checking the option to "show items with no data," it works as intended, except the range of dates it's showing no data is ridiculous. It shows all of 2017 and all of 2020, but I want it to only show items with no data within the min and max months of the visible data.Capture.PNG

 

 

9 REPLIES 9
v-gizhi-msft
Community Support
Community Support

Hi,

 

According to your description, i create a simple sample to test:

183.PNG

Please try to create a new calculated table:

X-axis Table = 
SELECTCOLUMNS (
    FILTER (
        'Table',
        'Table'[Date]
            >= CALCULATE (
                MIN ( 'Table'[Date] ),
                FILTER ( 'Table', 'Table'[Sales] <> BLANK () )
            )
            && 'Table'[Date]
                <= CALCULATE (
                    MAX ( 'Table'[Date] ),
                    FILTER ( 'Table', 'Table'[Sales] <> BLANK () )
                )
    ),
    "Date", 'Table'[Date]
)

Then create relationship between two tables:

181.PNG

Choose this table's [Date] column as column chart visual's X-axis, the result shows:

182.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

amitchandak
Super User
Super User

Try

Min Max 1= 
var _min = minx(sales,sales[sales date])
var _max = maxx(sales,sales[sales date])
return
CALCULATE(sum(sales[Value]),Date[date]<=_max ,Date[date]>=_min )


Min Max 1= 
var _min = minx(sales,sales[sales date])
var _max = maxx(sales,sales[sales date])
return
CALCULATE(sum(sales[Value]),filter(all(Date),Date[date]<=_max ,Date[date]>=_min ))

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

Thank you Amitchandak.

I had to modify for my use case, posting here as may be helpful to others:

Housekeeping values (KPI) =
//Shows full reporting range in KPI cards
//shows 0 for dates without data (as zero data is not recorded in source)

VAR MinDate = MIN ( 'Report Date'[ExtractDate] )
VAR MaxDate = MAX ( 'Report Date'[ExtractDate] )
RETURN
    CALCULATE (
        [Avg Value],
        'Report Date'[ExtractDate] <= MaxDate,
        'Report Date'[ExtractDate] >= MinDate
    ) + 0

Will your code show 0 or a blank when value is 0?  How would you edit it to show 0 for blank months if I wanted that?

This is just not working for me and i dont know why. I'm using a column chart and it's still showing the x-axis for ranges where data exists, but outside of the filter. I have no idea what Im doing wrong.

 

kmrastegar_0-1678785518575.png

 

here is the measure:

Days Lost =
VAR MinDate = MIN ( Absences[Absence Start] )
VAR MaxDate = MAX ( Absences[Absence Start] )
RETURN
    CALCULATE (
        SUM(Absences[Days Absent]),
        Absences[Absence Start] <= MaxDate,
        Absences[Absence Start] >= MinDate
    ) + 0

I'm not familiar with creating variables like this. Can you walk me through how I would set that up?

Hi,

 

The formula i provided last time was to create a specific column in order to replace the original X-axis.

I am sorry i haven't shared my test pbix file with you.

Here is my file:

pbix 

 

Best Regards,

Giotto Zhi

kentyler
Solution Sage
Solution Sage

How have you tried to limit the visual to data between your min and max months ?





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


I tried, but it doesn't restrict the range of no item months.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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