Forum Discussion

BBECQ's avatar
BBECQ
Regular Visitor
1 year ago
Solved

Do not visualize data on graph after today (?)

Hi all,

This is my DAX formula to calculate MAT values = 

MAT Test =
CALCULATE (
    SUM(MonthlySalesBeLux[Ex Fac €]),
    FILTER (
        DATESINPERIOD(
            DateTable[Date],
            max(DateTable[Date]),
            -12,
            MONTH
        ),
        DateTable[Date] <= TODAY()
    ),
    MonthlySalesBeLux[Ex Fac €] <> 0
)
 
When I visualize, I get this:


I am convinced there should be an easy way to adapt my DAX formula do it doesn't calculate (returns a blank) for all dates in my visual after today ... This is basic no? 

I have a 'source file' with in the rows the sales for different product per month.
I have linked a DateTable ... 




Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi BBECQ, hello all, thank you for your prompt reply!

     

    Update the measure as shown below:

    MAT Test = 
    CALCULATE(
        SUM(FactTable[Ex Fac €]),
        FILTER (
            DATESINPERIOD(
                DateTable[Date],
                MAX(DateTable[Date]),
                -12,
                MONTH
            ),
            DateTable[Date] <=MAX(FactTable[Date])
        ),
        FactTable[Ex Fac €] <> 0  
    )

    Result:

     

    Best regards,

    Joyce

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    1 year ago

    BBECQ , Try like

    example measure 

    if( max(DateTable[Date])  <= TODAY() , CALCULATE (
    SUM(MonthlySalesBeLux[Ex Fac €]),DATESINPERIOD(
    DateTable[Date],
    max(DateTable[Date]),
    -12,
    MONTH
    ) ) , Blank() )

  • speedramps's avatar
    speedramps
    Super User
    1 year ago

    try this 

     

    it will only return a value if the date x-axis is less than today

     

    Please click thumbs up and accept solution, thanks

     

    IF(
SELECTEDVALUE(DateTable[Date]) <= TODAY(),
[yourmeasurename],   /// or your measure formula
BLANK()
)



  • BBECQ's avatar
    BBECQ
    Regular Visitor
    1 year ago

    Hi amitchandak speedramps 

    These solutions seem to work when I add the dates from my source file on the X axis ... But then it filters only the last 12 months 😞
    When I add the months from my datetable I made, the solution doesn't work at all - still a flat line on the right. 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi BBECQ, hello all, thank you for your prompt reply!

       

      Update the measure as shown below:

      MAT Test = 
    CALCULATE(
        SUM(FactTable[Ex Fac €]),
        FILTER (
            DATESINPERIOD(
                DateTable[Date],
                MAX(DateTable[Date]),
                -12,
                MONTH
            ),
            DateTable[Date] <=MAX(FactTable[Date])
        ),
        FactTable[Ex Fac €] <> 0  
    )

      Result:

       

      Best regards,

      Joyce

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.