Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create a calculated Column for is previous month

Hi all, I would like to create a calculated column that shows me if that date is in the previous month yes or no. How should I do this? I already have a calculated column for is current month a...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    For data you can use datesmtd and totalmtd with date table

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
2nd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

     

    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/

dobregon's avatar
dobregon
Impactful Individual
6 years ago

i think you are doing many many many difficult things for something that maybe it is very simple. example:

Imagine that you have a table with sales values by date or month or whatever.

1. you want to know the sum of Sales in the matrix table for the period selected or viewed in the matrix, you will do something like this:

 

Current Sales =CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]))

 

2. You can create similar measure from the previous month

 

Currante Sales PrevM=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSMONTH('DateTime'[DateKey]))

 


With both measures you can have matrix table, line charts very beautifull and this second measure give to you the idea that you can have measures for the previous year too but the same month.

And if what you want it is the column calculated you can create the column calculated using the same code.


  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    dobregon thanks for your answer. 

    The thing is I want to have a card with total expenses previous month and a card with total expenses the month before that and then send alerts when one exceeds the other ( or maybe even a card with expenses this month and expenseslast month but that depends on the datarefresh frequency). Would this also be possible with measures?

    thanks!

    • dobregon's avatar
      dobregon
      Impactful Individual
      6 years ago

      yes Anonymous  with both measures you can create the values and other to the alert

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      Try like

      IsPreviousMonth =
var _date = date(year(TODAY () ),MONTH ( TODAY ()-1 ),day(TODAY ()))
return
IF (
YEAR ( DimDate[full date] ) = YEAR (_date )
&& MONTH ( DimDate[full date] ) = MONTH ( _date );
"Yes";
"No"
)
      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        For data you can use datesmtd and totalmtd with date table

        MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
2nd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

         

        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/