take max date from calendar table and pass it to fact table

[rob_vander2]

Hi All,

I have two tables. Fact table and calendar table. Both tables are linked on date. My requirement is, I want to show sales value from fact table on latest date in KPI. Note that user can select any specific date from calendar, so sales value should reflect that value as well. How do I write DAX measure to achive this? I tried below

VAR  max_date = max(fact_table[business_date])
return
calculate ( sum(fact_table[sales])), fact_table[business_date] = max_date)

This works by default, but when I select any date from calendar, it shows blank. I want to show the value for that specific date

[sjoerdvn]

If the fact and calendar table have a relationship and you are selecting single dates on the calendar table, then the filter on max date is not needed, the fact table will be filtered anyway.
However, even if not needed, that filter should not get in the way. 
So what is really this issue here is hard to say without access to the actual model and report, but it could be that the values in calendar and fact do not match, like the calendar values are dates and the fact table values contain date and time part.

[v-nmadadi-msft]

Hi @rob_vander2 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[theov]

I recommend you creationg a column with the last date and filter your report on it. here it is explained: https://youtu.be/E_-NsH1o308?si=aDXU8sY3yc6n3ezy

[v-nmadadi-msft]

Hi @rob_vander2 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[Kedar_Pande]

Your measure uses the fact table date. Use the calendar table date instead.

 

Sales Measure =
VAR SelectedDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
SUM(fact_table[sales]),
fact_table[business_date] = SelectedDate
)

@rob_vander2

[Jihwan_Kim]

Hi, I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I tried to show latest date sales per each country, and each country shows different latest date. And I tried to use TREATAS DAX function in the measure.

TREATAS function - DAX | Microsoft Learn

Last date sales: =
VAR _periodselect =
    MAX ( 'calendar'[Date] )
VAR _t =
    SUMMARIZECOLUMNS (
        country[country],
        'calendar'[Date],
        FILTER ( ALL ( 'calendar' ), 'calendar'[Date] <= _periodselect ),
        "@sales", SUM ( fact_table[sales] )
    )
VAR _latestdate =
    ADDCOLUMNS (
        _t,
        "@latestdate",
            MAXX (
                FILTER ( _t, country[country] = EARLIER ( country[country] ) ),
                'calendar'[Date]
            )
    )
VAR _treatas =
    SUMMARIZE ( _latestdate, country[country], [@latestdate] )
RETURN
    CALCULATE (
        SUM ( fact_table[sales] ),
        TREATAS ( _treatas, country[country], 'calendar'[Date] )
    )

[Nasif_Azam]

Hey  @rob_vander2 ,

To meet the requirement where the sales value reflects the selected date from the calendar and also displays the sales for the latest date, you can try the DAX measure like this:

SalesOnSelectedDate :=
VAR SelectedDate = MAX('Calendar'[Date]) 
VAR MaxDate = CALCULATE(MAX('FactTable'[BusinessDate]), ALL('FactTable'))
VAR DateToUse = IF(SelectedDate = BLANK(), MaxDate, SelectedDate)
RETURN
CALCULATE(SUM('FactTable'[Sales]), 'FactTable'[BusinessDate] = DateToUse)

 

Best Regards,
Nasif Azam

[Praful_Potphode]

Hi @rob_vander2 

since you have calendar table ,all the date filters should be written on that.calendar table is connected to your fact so if you filter calendar, it will eventually filter fact.

please try below measure and let me know if it works.

Measure=
VAR  max_date = max(Calendar[date])
return
calculate ( sum(fact_table[sales])),ALL(Calendar), Calendar[date] = max_date)

the ALL function removes all filters from calendar table.then it applies the max date filter.

for this to work create your date slicer on calendar.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful