Forum Discussion

TechInayam's avatar
TechInayam
Regular Visitor
2 years ago

Dax issue

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

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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's avatar
      TechInayam
      Regular Visitor
      2 years ago

      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?

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.