Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help with calculations

I need to create a line chart with monthl by month figures.  I get a report each month that I need to complete multiple steps in Excel to create this chart.  I want to be able to create this in Power...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi,

    I did this in two measures. Hope this helps!

    UnitTotal = SUMX('Table', 'Table'[L25]+'Table'[L27])

Monthly Total = 
VAR PrevMonthUnitTotal = CALCULATE([UnitTotal],
                         PREVIOUSMONTH('Table'[Report Date])
                         )
Return
IF(SELECTEDVALUE('Table'[This Month]) = "October",
    [UnitTotal],
    [UnitTotal]-PrevMonthUnitTotal
    
 )

     

    ~Kim

amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , with help from date table and time intelligence 

MTD Sales = CALCULATE(SUM(Sales[L25]+SUM(Sales[L27]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[L25]+SUM(Sales[L27]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
or
last MTD Sales = CALCULATE(SUM(Sales[L25]+SUM(Sales[L27]),dateadd('Date'[Date],-1,MONTH))

Current Month = [MTD Sales] -[last MTD Sales]

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/