Line chart with min and max date values from calendar (not from events)

[vonschultz666]

Hello everyone, i need help with line chart and custom date range based on my min and max calendar dates, not the one based on sales dates.

This is the model:

I'm calculating sum of sales on the line chart and putting red markers on line chart for certain events. I want to achieve min and max calendar timespan on my line chart, not the one representing sales.

I've tried different formulas, but still not getting any succes.
Example 1:

SalesQuantityTEST=
CALCULATE(
    SUM('[RetailSalesPerStorePerItemPerDay]'[SalesQuantity]),
    ALL('[Calendar]'[Date])
) +0

Results in the following (for some reason i'm gettin line chart until the end of 2024, altough i don't have any date value after Feb. 2024). This is the desired outcome, but withouth whole 2024:

Example 2:

SalesQuantityTEST=
VAR MinDate = MIN('[Calendar]'[Date]) -- Get the minimum date from the Calendar table
VAR MaxDate = MAX('[Calendar]'[Date]) -- Get the maximum date from the Calendar table
RETURN
CALCULATE(
    SUM('[RetailSalesPerStorePerItemPerDay]'[SalesQuantity]),
    FILTER(
        ALL('[Calendar]'), -- Remove any filters from the Calendar table
        '[Calendar]'[Date] >= MinDate && '[Calendar]'[Date] <= MaxDate
    )
)

Results in this (sales line events are represented only for the dates for sales, not from the calendar. Furtheremore, first sales happend after the red marker event so i want to have sales line for zero values. In that way sales line and red markers would align):

Second value (marker) is represented by the following and i'm getting above mentioned problems with and without the following calculation, so the problem doesn't lie in this one:

▲ Quantity On Date = 
 IF(
    MAX('[ItemAssortmentCodeHistoryTimeline]'[ChangeDate]) = BLANK(),
    BLANK(),
    IF(
        '[RetailSalesPerStorePerItemPerDay]'[SalesQuantityTEST] = 0,
        0,
        '[RetailSalesPerStorePerItemPerDay]'[SalesQuantityTEST]
    )
)

 
Data set is confidental and large so

[Daniel29195]

@vonschultz666 

if i understood, you want to show all the dates , where date <= max ( date in sales table ) 

if this is your intention , 

create a calculated column in calendar table, as follow : 

cc = 

var maxsalesdate =  max(sales_table[date_column]) 

return

switch(true() , 

date<= maxsalesdate , 1 ,0 ) 

now, set this column in the filter pane -- filter on page .

lastly, select the show items with no data . ( before last item in the list ) 

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[vonschultz666]

Hi @Daniel29195 tnx for such a quick reply!

Before i try this, i just want to make sure we are on the same page: will this work if i have date from sales table which happend before max date in calendar, ie. will sales line continue until the max date from calendar (ie. line would be flat with zero sales)?

In that case date(sales) < date (calendar) but max date from date(calendar) would be shown on the line chart but with the flat line aligned to zero.

Furthermore, can i use the following dax measure in that case:

SalesQuantityTEST=
CALCULATE(
    SUM('[RetailSalesPerStorePerItemPerDay]'[SalesQuantity]),
    ALL('[Calendar]'[Date])
) +0

[Daniel29195]

@vonschultz666 

with the solution i have provided, i will explain the output with an example 

if you have dimcalendar ( from 2023-01-01 till 2030-01-01)

and sales table with its maxdate = 2024-02-22

you create a column in dimcalendar with the dax i mentioned above . 

now the calculated column will return true for all rows in calendar where date in calendar <=2024-02-22

now when you add this to the filter pane ,  filter on page. 

and then you create your visual, with date coming from calendar, and the measure you mentioned in your reply  : 

SalesQuantityTEST=
CALCULATE(
    SUM('[RetailSalesPerStorePerItemPerDay]'[SalesQuantity]),
    ALL('[Calendar]'[Date])
) +0

the data will be displayed for all dates from 2023-01-01 till 2024-02-22 

now of coursem if you want to remove 2023-01-01 becuase you dont have sales dates in thi year, 

you can modify the code i wrote, to add the min(sales[date])  and then 

if calendar[date] between mindate and maxdate then return 1 else return 0 

[vonschultz666]

Hi @Daniel29195 , unfortunately it isn't working.
I've created a calculated column in calendar table as follows:

DateVerification = 
VAR MaxSalesDate=MAX('[RetailSalesPerStorePerItemPerDay]'[PostingDate])
RETURN
SWITCH(TRUE(),'[Calendar]'[Date]<=MaxSalesDate, 1, 0)

Added it to the page filter:

Date from calendar table (which is used on the line chart) is marked for "Show items with no data"

...and the end results is still the same:

I'm still using:

SalesQuantityTEST=
CALCULATE(
    SUM('[RetailSalesPerStorePerItemPerDay]'[SalesQuantity]),
    ALL('[Calendar]'[Date])
) +0

I do not get it why the timeline constantly goes until the end of 2024 while my last data point in both tables is one or two days ago...

[vonschultz666]

@Daniel29195i've managed to find a partial solution.
I've marked Calendar as a date table and createad the following column as data type "date":

YYYYYMM = 
FORMAT('[Calendar]'[Date], "YYYY-MM")

Afterwards, i've put YYYYMM date on the line chart (horizontal axis) and got what i wanted:

Nevertheless, with marking Calendar table as a date table, i've lost hiearchy and drilling capabilities on a line chart. Is it possible to achieve something similar without losing hiearchy?