Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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]))
@MSuser5 , Please find the attached file
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.
EOM Sales = CALCULATE(SUM(Sales[Sales Amount]), 'Date'[Date] = EOMONTH(MAX('Date'[Date]), 0))
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))
it's showing 0 ( both Measures) attached PBIX with source data
https://drive.google.com/file/d/18H10CWPMJrCWdW-1pBSyQ52z6gPO9u7a/view?usp=sharing
@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
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
@MSuser5 , Please find the attached file
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]))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |