Forum Discussion

ApurvaKhatri's avatar
ApurvaKhatri
Helper III
8 years ago
Solved

Get value for previous month

Data   GroupingPoint GroupingCount GroupingDate EndOfPeriod 50 2017-08-31 EndOfPeriod 45 2017-07-31 EndOfPeriod 49 2017-06-30 EndOfPeriod 54 2017-05-31 EndOfPeriod 58 2017-04-30 EndOfPeriod 5...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi ApurvaKhatri

     

    Add this calculated column

    PreviousMonthCount =
VAR Previous_Month =
    MAXX (
        FILTER ( Table1, Table1[GroupingDate] < EARLIER ( Table1[GroupingDate] ) ),
        Table1[GroupingDate]
    )
RETURN
    CALCULATE (
        SUM ( Table1[GroupingCount] ),
        FILTER ( Table1, Table1[GroupingDate] = Previous_Month )
    )


  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi ApurvaKhatri

     

    Alternatively, you could use a "MEASURE" as well


    PreviousMonthCount =
VAR Previous_Month =
    MAXX (
        FILTER (
            ALL ( Table1 ),
            Table1[GroupingDate] < VALUES ( Table1[GroupingDate] )
        ),
        Table1[GroupingDate]
    )
RETURN
    IF (
        HASONEVALUE ( Table1[GroupingDate] ),
        CALCULATE (
            VALUES ( Table1[GroupingCount] ),
            FILTER ( ALL ( Table1 ), Table1[GroupingDate] = Previous_Month )
        )
    )
Anonymous's avatar
Anonymous
Not applicable
8 years ago

You need a proper calendar table created, with a continuous date column (every date in the year, even if your fact table doesn't use all the dates).

 

Create a relationship between the Calendar[Date] and your Table[GroupedDate]

 

Since your fact table has monthly granularity roll-up, you can use the following measures:

 

[Total Amount] =
SUM(Table[GroupedCount])

and 

 

Previous Amount =
CALCULATE ( [Total Amount], PREVIOUSMONTH ( Calendar[Date] ) )
ApurvaKhatri's avatar
ApurvaKhatri
Helper III
8 years ago

Is it possible to have it without the calender table

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Best practice dictates using a calendar table whenever you're adding time intelligence measures.  I STRONGLY recommend reconsidering.  The initial effort to build a calendar table will FAR outweigh the needless complexity that your DAX measures will need to make up for the fact that a calendar table is missing.

     

    Please refer to these resources:

     

    https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

     

    https://powerpivotpro.com/2016/01/year-to-date-in-previousprior-year/

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Hi ApurvaKhatri

       

      Add this calculated column

      PreviousMonthCount =
VAR Previous_Month =
    MAXX (
        FILTER ( Table1, Table1[GroupingDate] < EARLIER ( Table1[GroupingDate] ) ),
        Table1[GroupingDate]
    )
RETURN
    CALCULATE (
        SUM ( Table1[GroupingCount] ),
        FILTER ( Table1, Table1[GroupingDate] = Previous_Month )
    )


      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Hi ApurvaKhatri

         

        Alternatively, you could use a "MEASURE" as well


        PreviousMonthCount =
VAR Previous_Month =
    MAXX (
        FILTER (
            ALL ( Table1 ),
            Table1[GroupingDate] < VALUES ( Table1[GroupingDate] )
        ),
        Table1[GroupingDate]
    )
RETURN
    IF (
        HASONEVALUE ( Table1[GroupingDate] ),
        CALCULATE (
            VALUES ( Table1[GroupingCount] ),
            FILTER ( ALL ( Table1 ), Table1[GroupingDate] = Previous_Month )
        )
    )