Forum Discussion

usershan's avatar
usershan
New Member
4 years ago
Solved

DAX Month to date

Hi All,

I have a formula in Oracle BI that needs to be converted to Power BI Dax measure.

Oracle BI - ToDate(SUM(table.column1), [Level Fiscal Month])

 

Can you all help me in this? How to write this in Power BI? I tried using calculate and DatesMTD, it is not working.

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi usershan 

    If you want to calculate the data of the current month up to the current maximum date, you can use this formula.

    DATESMTD : Returns a table that contains a column of the dates for the month to date, in the current context.

    MTD = CALCULATE(SUM('Table'[value]),DATESMTD('Table'[Date]))

    If you are not familiar with DATESMTD, DATESQTD, DATESYTD, you can refer to my pbix file, I listed different DAX applications scenarios in it . Hope it will help you !

     

    Best Regard

    Community Support Team _ Ailsa Tao

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    usershan , if you need MTD, Datesmtd with date table should help

     

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

     

     

    or

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

     

    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

     

     

    • Karthik12's avatar
      Karthik12
      Icon for Post Patron rankPost Patron
      3 years ago

      amitchandak Hi, Is there way to create a measure from start date to till date of the month. For example, I have Plan sheet which has day wise plan numbers till end of this month (From 01-01-2023 to 31-01-2023) but I want to know the sum of plan qty till today. Is it possible?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi usershan 

    If you want to calculate the data of the current month up to the current maximum date, you can use this formula.

    DATESMTD : Returns a table that contains a column of the dates for the month to date, in the current context.

    MTD = CALCULATE(SUM('Table'[value]),DATESMTD('Table'[Date]))

    If you are not familiar with DATESMTD, DATESQTD, DATESYTD, you can refer to my pbix file, I listed different DAX applications scenarios in it . Hope it will help you !

     

    Best Regard

    Community Support Team _ Ailsa Tao