Get value for previous month

[ApurvaKhatri]

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  

[Zubair_Muhammad · Accepted solution]

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

[Anonymous]

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]

Is it possible to have it without the calender table

[Anonymous]

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 · Accepted solution]

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 · Accepted solution]

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