Dax issue

[TechInayam]

Help required

Trying more than 2 days

Working on calculating year month wise

MTD - month to date
PMTD - previous month to date

I have Date master and sales fact

Working fine year , month draged from date hierachy

Issue

I need to have data as fiscal year and fiscal month these are new columns i calculated and datatypes is int not date

If i dragged the fiscal year

Mtd and pmtd are same for month and
On apply Filtered also wierdly working , 4 months nov, dec ,jan, feb i have selected but oct also visible in table

Formulas

MTD =
VAR LastDayAvailable = DAY(TODAY())
VAR LastFiscalYearMonthAvailable = MAX ( 'Date'[Year Month No] )
VAR Result =
CALCULATE (
[Sales],

'Date'[Day] <= LastDayAvailable,
'Date[Year Month No] = LastFiscalYearMonthAvailable
)
RETURN
Result

PMTD = CALCULATE([MTD], DATEADD('Date'[Date].[Date], -1, MONTH))

[Anonymous]

Hi @TechInayam ,

Please try this DAX:

Fiscal MTD = 
VAR CurrentFiscalMonthStart = CALCULATE(MIN('Date'[FiscalStartDate]), 'Date'[FiscalYearMonthNo] = MAX('Date'[FiscalYearMonthNo]))
VAR Result = CALCULATE([Sales], 'Date'[Date] >= CurrentFiscalMonthStart, 'Date'[Date] <= TODAY())
RETURN Result
 
Fiscal PMTD = 
VAR CurrentFiscalMonthStart = CALCULATE(MIN('Date'[FiscalStartDate]), 'Date'[FiscalYearMonthNo] = MAX('Date'[FiscalYearMonthNo]))
VAR PreviousFiscalMonthStart = EDATE(CurrentFiscalMonthStart, -1)
VAR PreviousFiscalMonthEnd = EOMONTH(PreviousFiscalMonthStart, 0)
VAR Result = CALCULATE([Sales], 'Date'[Date] >= PreviousFiscalMonthStart, 'Date'[Date] <= PreviousFiscalMonthEnd)
RETURN Result

If not, can you provide a sample data or pbix file after removing the privacy data?

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@TechInayam , if there is selection , you can use datesmtd

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

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

MTD, LMTD  Based on Today

MTD Today =
var _min = eomonth(today(),-1)+1
var _max = today()
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

LMTD Today=
var _min = eomonth(today(),-2)+1
var _max1 = today()
var _max = date(year(_max1),month(_max1)-1, day(_max1))
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

LYMTD Today =
var _min = eomonth(today(),-13)+1
var _max1 = today()-1
var _max = date(year(_max1)-1,month(_max1), day(_max1))
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

MTD Yesterday =
var _max = today() -1
var _min = eomonth(_max,-1)+1
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
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

[TechInayam]

I Have Tried But Results are different from Expectation ,  and am not   understanding the LMTD Behaviour , We calculate against Feb month but showing for January month , Am i Missing any thing?