Need help : based on slicer month selection get previous and current sales values

[Anonymous]

Hi All,

i have seles and target with datewise if i selection month from slicer i need to get each month last date target and sales values (ex: if i select Feb'21 then i should get last year, last month, current month values ) attached here 

[amitchandak · Accepted solution]

@MSuser5 , In measure use a measure like

lastnonblankvalue('Date'[Date],SUM(Sales[Sales Amount]))

last MTD Sales = CALCULATE(lastnonblankvalue('Date'[Date],SUM(Sales[Sales Amount])),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(lastnonblankvalue('Date'[Date],SUM(Sales[Sales Amount])),previousmonth('Date'[Date]))

[Anonymous]

Hi Amit,

i need only last date value for each month. MTD it's making sum of entire month 

[mwegener]

Hi @Anonymous,

with EOMONTH you can determine the last date of a month.

https://dax.guide/eomonth/

EOM Sales = CALCULATE(SUM(Sales[Sales Amount]), 'Date'[Date] = EOMONTH(MAX('Date'[Date]), 0))

[MSuser5]

Hi @mwegener ,

Thanks for the update but how to get previous last day value ? and previous month value as well ?

please give me dax for previous month last day ?

Thanks

[mwegener]

EOM-1 Sales = CALCULATE(SUM(Sales[Sales Amount]), 'Date'[Date] = EOMONTH(MAX('Date'[Date]), 0)-1)

EOPM Sales = CALCULATE(SUM(Sales[Sales Amount]), 'Date'[Date] = EOMONTH(MAX('Date'[Date]), -1))

[MSuser5]

@mwegener ,

it's showing 0 ( both Measures) attached PBIX with source data

https://drive.google.com/file/d/18H10CWPMJrCWdW-1pBSyQ52z6gPO9u7a/view?usp=sharing

[amitchandak]

@Anonymous , You can use date table and time intelligence for that. Join date table with both tables and create measures like examples

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LYTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[MSuser5]

@amitchandak ,

Thanks!
Above mentioned DAX calculating sum of month value but as per requirement i need month last day value same like previous month last day value attached screenshot for ref.

Kindly help me

[amitchandak · Accepted solution]

@MSuser5 , Please find the attached file

[Anonymous]

Hi Amit,

thanks for the update but i need to get each month last date values so MTD, YTD ideas won't support kindly help me attached source file below onedrive please check
https://drive.google.com/file/d/18H10CWPMJrCWdW-1pBSyQ52z6gPO9u7a/view?usp=drivesdk

[amitchandak · Accepted solution]

@MSuser5 , In measure use a measure like

lastnonblankvalue('Date'[Date],SUM(Sales[Sales Amount]))

last MTD Sales = CALCULATE(lastnonblankvalue('Date'[Date],SUM(Sales[Sales Amount])),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(lastnonblankvalue('Date'[Date],SUM(Sales[Sales Amount])),previousmonth('Date'[Date]))