set target goals value always show lastday of the month

[hariepermana]

Hello expert,

i've a data (sales amount) on my database and i update it everyday.

i make a kpi card and i wanna compare every sales amount (everyday) with monthly target. the problem is, i've date measure on my page and everytime i drag it day by day, it wont compare with monthly target (show blank) except i drag it on a last day of the month.

can you help me, which kind of DAX can solve my problem.

and here my sample data

1. Table Sales

    date | amount_sales

    2020-10-01 | 100

    2020-10-02 | 200
    2020-10-03 | 300
2. Table target_goals
    date | amount_target

    2020-10-31 | 2000
3. Table master_calendar

and here my latest DAX (target goals)

target_amount = VAR latestDate = MAX('master_calendar'[date]) RETURN CALCULATE(SUM('target_goals'[amount_target]),'target_goals'[date]=latestDate)

i appreciate every help
thank you

[amitchandak · Accepted solution]

@hariepermana , For target you can use closingbalancemonth and date table. As that is saved on last date.

That has been discussed in details in my video :https://youtu.be/yPQ9UV37LOU

[hariepermana]

@amitchandak , thank for your suggestion, it 100% work!