Get the sum of latest months sales to display in a card

[Enigma]

Hi, 

There's a table with sales figures per day as rows. I need to get the sum of sales of the latest month only and show it in a card visual.
I'm a noob with DAX so can you please help me figure this out?

[v-luwang-msft · Accepted solution]

Hi @Enigma ，

You also could use the following dax:

meausre = var first= EOMONTH(MAX('Table'[date]),-1)
var second=CALCULATE (
    AVERAGE( 'Table'[value] ),
    ALL ( 'Table' ),
      'Table'[date]>first
       )
  return second

the two measure return the same result:

Wish it is helpful for you!

Best Regards

Lucien

[Enigma]

Thanks for helping out!
@ERD - I had checked that page before and it was not exactly what I needed.
@v-luwang-msft - I only need the score for the latest month and not the YTD.

I wrote the following and it worked. 

AvgQCScore Latest Month = 
VAR latestMonth = MONTH(MAX(tblSherlockRaw[Case End Date]))
VAR latestYear = YEAR(MAX(tblSherlockRaw[Case End Date]))
RETURN
CALCULATE(
    AVERAGE(tblSherlockRaw[TOTAL_QC_SCORE]),
    MONTH(tblSherlockRaw[Case End Date]) = latestMonth,
    YEAR(tblSherlockRaw[Case End Date]) = latestYear
)

Any improvements you can suggest in the above?

[v-luwang-msft]

Hi @Enigma ，

You could use the below measure:

measure =
CALCULATE (
    SUM ( 'Table'[value] ),
    ALL ( 'Table' ),
    'Table'[date]
        >= IF (
            MONTH ( TODAY () ) > 1,
            DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) ),
            DATE ( YEAR ( TODAY () ) - 1, 12, DAY ( TODAY () ) )
        )
)

the base data:

and final you will get:

You want get the the sum of sales of the latest month ， in my measure ,i will get the sum from 2021/3/19 to today,

and when actual data is January， you will get the sum from last December to  January.

Wish it is helpful for you!

Best Regards

Lucien

[ERD]

Hi, @Enigma ,

Have a look at this: https://expert-only.net/dax-functions/calculate-last-month-value-dax-power-bi/