Forum Discussion

Midway's avatar
Midway
Helper I
1 year ago
Solved

help with DAX MTD

Hi there, 

I'm really struggling with this calculation with this DAX measure; What I need to accomplish is show let's sales numbers in a card. When a month is selected in another visual (like a timeline), the card should show the "sales" numbers for the selected month and that is working perfectly, what is not working is when no selection, the card should default to show "sales" for the current month, and that's is not working at all.

This is DAX I'm using right now;

 

CALCULATE(
    SUM(Fact[SALES]),
   DATESMTD(DimDate[Date])
)

Any help would be highly appreciated

 

  • Deku's avatar
    Deku
    1 year ago

    var dt = eomonth( MAXX( summarize( fact, DimDate[Date] ), dimdate[date]), 0)

    return

    CALCULATE(

        SUM(Fact[SALES]),

        DATESBETWEEN( DimDate[Date], EOMONTH( dt, -1) + 1, dt )

    )

12 Replies

  • Deku's avatar
    Deku
    Super User
    1 year ago
    var dt = max( DimDate[Date] )
return
CALCULATE(
    SUM(Fact[SALES]),
    KEEPFILTERS( DATESBETWEEN( DimDate[Date], EOMONTH( dt, -1) + 1, dt ), dt ) )
)
    • Midway's avatar
      Midway
      Helper I
      1 year ago

      hey there, thanks for replying to my post. I tried this formula but it's giving an error, "unexpected parameter , dt". If I remove runs fine but still does not work. Still no sales without selecting any data points.

  • Pow3Range's avatar
    Pow3Range
    Frequent Visitor
    1 year ago

    UPDATED 
    -------
    VAR DATE_ =
    IF(
    ISFILTERED(DimDate[Date]),
    MAX(DimDate[Date]),
    TODAY()
    )
    RETURN
    CALCULATE(
    SUM(Fact[SALES]),
    DATESBETWEEN(DimDate[Date], EOMONTH(DATE_, -1) + 1, EOMONTH(DATE_, 0))
    )

    • Midway's avatar
      Midway
      Helper I
      1 year ago

      Thanks for replying to my post, this gets the default sales numbers for the current month when no selection has been made, but it keeps the same current month sales numbers when selecting other months.

  • techies's avatar
    techies
    Super User
    1 year ago

    Hi Midway please check this

     

    ResultMeasure=

    VAR IsMonthSelected = ISFILTERED('DATE'[Month])

    VAR DefaultMonthSales =
    CALCULATE(
    SUM('sheet1'[enrollment amount]),
    FILTER(
    ALL('DATE'),
    'DATE'[Year] = YEAR(TODAY()) && 'DATE'[Month] = MONTH(TODAY())
    )
    )

    VAR SelectedMonthSales =
    CALCULATE(
    SUM('sheet1'[enrollment amount]),
    DATESMTD('DATE'[Date])
    )

    RETURN
    IF(
    IsMonthSelected,
    SelectedMonthSales,
    DefaultMonthSales
    )

    • Midway's avatar
      Midway
      Helper I
      1 year ago

      Thanks for replying to my post, this gets the default sales numbers for the current month when no selection has been made, but it keeps the same current month sales numbers when selecting other months.

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Midway Use DAX
    SalesMeasure :=
    IF(
    ISFILTERED(DimDate[Month]),
    CALCULATE(
    SUM(Fact[SALES])
    ),
    CALCULATE(
    SUM(Fact[SALES]),
    DATESMTD(DimDate[Date])
    )
    )