cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Enigma
Helper III
Helper III

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

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?

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

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

 

v-luwang-msft_0-1618999366861.pngv-luwang-msft_1-1618999376436.png

the two measure return the same result:

v-luwang-msft_2-1618999416897.png

 

 

Wish it is helpful for you!

 

 

Best Regards

Lucien

View solution in original post

4 REPLIES 4
v-luwang-msft
Community Support
Community Support

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

 

v-luwang-msft_0-1618999366861.pngv-luwang-msft_1-1618999376436.png

the two measure return the same result:

v-luwang-msft_2-1618999416897.png

 

 

Wish it is helpful for you!

 

 

Best Regards

Lucien

View solution in original post

Enigma
Helper III
Helper III

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
Community Support
Community Support

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:

v-luwang-msft_0-1618816647341.png

and final you will get:

v-luwang-msft_1-1618816668765.png

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
Solution Sage
Solution Sage

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors