Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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 Capture1.JPG

2 ACCEPTED SOLUTIONS

@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]))

View solution in original post

@MSuser5 , Please find the attached file

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

Hi Amit,

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

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))

 

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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

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))

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


@mwegener ,

 

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

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

amitchandak
Super User
Super User

@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

@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 meCapture1.JPG

@MSuser5 , Please find the attached file

Anonymous
Not applicable

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

@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]))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.