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