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 52 2017-03-31
EndOfPeriod 65 2017-02-28
EndOfPeriod 55 2017-01-31

 

Based on groupingdate I need  to print grouping count.

 

e.g

groupingdate  groupingcount

2017-07-31        49 

 

for month Aug, i.e 2017-08-31 I need to print the value of previous month e.g 45

 

How is this possible?

 

Thanks  

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

5 Replies

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