Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dax Measure respecting dates

I have 3 tables: Invoiced and Forecast, and Date.

 

I want to show invoiced amounts for any past month and forecast amount for any current or future month, in the same graph, but only in one measure (there is no point having both side-by-side if one of each is empty in every month).

 

I would like to create a measure: if(date<date(year(today()), month(today()), 1), sum(invoiced), sum(forecast)). 

 

The measure will be added as a Value to a graph with a Date axis. How can this be accomplished?
(Alternative would be to merge both tables on 'date' and create a column 'invoiced/forecast', but I would prefer a measure) 

  • Like this?

    Invoiced/Forcast =
    IF (
        MAX ( 'Date'[Date] ) <= EOMONTH ( TODAY (), -1 ),
        SUM ( Table1[invoiced] ),
        SUM ( Table2[forecast] )
    )

2 Replies

  • Like this?

    Invoiced/Forcast =
    IF (
        MAX ( 'Date'[Date] ) <= EOMONTH ( TODAY (), -1 ),
        SUM ( Table1[invoiced] ),
        SUM ( Table2[forecast] )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable

      Exactly what I was looking for. Thank you!