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

[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

[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

[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] )
    )

[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

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