Forum Discussion

MacJasem's avatar
MacJasem
Icon for Advocate II rankAdvocate II
3 years ago
Solved

Flatlined values in a MTD table

Hi Guys

Whats the esiest reusable method to discontinue flatlined values in YTD/MTD/WTD tables/graphs/BarCharts, such as this example:

Data stops at 2. of February and thereby the chart flatlines. I wish to see only the current data as new data gets loaded to the dataset.

DAX for the MTD measure is:

 

TotalSalesValueMTD =
CALCULATE([SalesValue], DATESMTD('Date'[Date]))

 

How do you guys do it?

  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, MacJasem 

     

    You can try the following methods.

    Measure = IF([TotalSalesValueMTD]=0,0,1)

    Result:

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

6 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    You could create a measure like

    Date is visible =
VAR LastSaleDate =
    CALCULATE ( MAX ( 'Sales'[Date] ), ALL ( 'Sales' ) )
VAR CurrentDate =
    MAX ( 'Date'[Date] )
RETURN
    IF ( CurrentDate <= LastSaleDate, 1 )

    and add that as a filter to the visual, to only show when the value is 1.

    • MacJasem's avatar
      MacJasem
      Icon for Advocate II rankAdvocate II
      3 years ago

      Hi johnt75 

       

      Thanks for the tip. I tried it but still didn't get the flatline discontinued. I might be doing something wrong?

      Could you explain in few words, for me and perhaps for others gain, what the measure does?

       

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        The principle is that the Date table will always have dates into the future, whereas the sales table would only have dates for which there are sales. You want the chart to stop at the last date on which there are sales, so you only want to show data when the maximum date visible in the current filter context, as generated by the visual, is on or before the last ever sales date. That is what you did in your FILTER.

  • MacJasem's avatar
    MacJasem
    Icon for Advocate II rankAdvocate II
    3 years ago

    I figured it out by entering a filter in the dax measure ny using the ALLSELECTED function like this;

     

    TotalSalesValueMTD =
    CALCULATE ([SalesValue],
        DATESMTD ( 'Date'[Date] ),
              FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX ( FactSales[OrderDate] )))

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, MacJasem 

     

    You can try the following methods.

    Measure = IF([TotalSalesValueMTD]=0,0,1)

    Result:

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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