Previous month sales by customer/product not working at a granular level

[Anonymous]

I put in the following formula 

PreviousMonthSales =

VAR CurrentMonth = SELECTEDVALUE('TPAMS Account Summary Combined'[CalendarMonth])

VAR CurrentYear = SELECTEDVALUE('TPAMS Account Summary Combined'[CalendarYear])

RETURN

IF(HASONEVALUE('TPAMS Account Summary Combined'[CalendarMonth]),

SUMX(

FILTER(

ALL('TPAMS Account Summary Combined'),

IF(CurrentMonth=1,

'TPAMS Account Summary Combined'[CalendarMonth]=12 &&

'TPAMS Account Summary Combined'[CalendarYear]=CurrentYear-1,

'TPAMS Account Summary Combined'[CalendarMonth]=CurrentMonth-1 &&

'TPAMS Account Summary Combined'[CalendarYear]=CurrentYear

)

),

[SumSales]

)

)

This works at the month level but if I put any further filters, I get repeated value for the month

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix files is attached in the end.

Table:

You may create measures as below.

Previous Month Customer = 
var tab =  
SUMMARIZE(
    'Table',
    'Table'[Customer],
    "Result1",
    CALCULATE(
        SUM('Table'[Sales Value]),
        FILTER(
            ALL('Table'),
            'Table'[Customer]=EARLIER('Table'[Customer])&&
            MONTH('Table'[Sale Date])=MONTH(TODAY())-1
        )
    )
)
return
SUMX(
    tab,
    [Result1]
)

Current Month Customer = 
var tab =  
SUMMARIZE(
    'Table',
    'Table'[Customer],
    "Result2",
    CALCULATE(
        SUM('Table'[Sales Value]),
        FILTER(
            ALL('Table'),
            'Table'[Customer]=EARLIER('Table'[Customer])&&
            MONTH('Table'[Sale Date])=MONTH(TODAY())
        )
    )
)
return
SUMX(
    tab,
    [Result2]
)

Difference Customer = 
var tab =  
SUMMARIZE(
    'Table',
    'Table'[Customer],
    "Result1",
    CALCULATE(
        SUM('Table'[Sales Value]),
        FILTER(
            ALL('Table'),
            'Table'[Customer]=EARLIER('Table'[Customer])&&
            MONTH('Table'[Sale Date])=MONTH(TODAY())-1
        )
    ),
    "Result2",
    CALCULATE(
        SUM('Table'[Sales Value]),
        FILTER(
            ALL('Table'),
            'Table'[Customer]=EARLIER('Table'[Customer])&&
            MONTH('Table'[Sale Date])=MONTH(TODAY())
        )
    )
)
return
SUMX(
    tab,
    [Result2]-[Result1]
)

%Change Customer = 
var tab =  
SUMMARIZE(
    'Table',
    'Table'[Customer],
    "Result1",
    CALCULATE(
        SUM('Table'[Sales Value]),
        FILTER(
            ALL('Table'),
            'Table'[Customer]=EARLIER('Table'[Customer])&&
            MONTH('Table'[Sale Date])=MONTH(TODAY())-1
        )
    ),
    "Result2",
    CALCULATE(
        SUM('Table'[Sales Value]),
        FILTER(
            ALL('Table'),
            'Table'[Customer]=EARLIER('Table'[Customer])&&
            MONTH('Table'[Sale Date])=MONTH(TODAY())
        )
    )
)
return
SUMX(
    tab,
    DIVIDE(
           [Result2]-[Result1],
           [Result1]
    )
)

Previous Month Product = 
var tab =  
SUMMARIZE(
    'Table',
    'Table'[Product],
    "Result1",
    CALCULATE(
        SUM('Table'[Sales Value]),
        FILTER(
            ALL('Table'),
            'Table'[Product]=EARLIER('Table'[Product])&&
            MONTH('Table'[Sale Date])=MONTH(TODAY())-1
        )
    )
)
return
SUMX(
    tab,
    [Result1]
)

Current Month Product = 
var tab =  
SUMMARIZE(
    'Table',
    'Table'[Product],
    "Result2",
    CALCULATE(
        SUM('Table'[Sales Value]),
        FILTER(
            ALL('Table'),
            'Table'[Product]=EARLIER('Table'[Product])&&
            MONTH('Table'[Sale Date])=MONTH(TODAY())
        )
    )
)
return
SUMX(
    tab,
    [Result2]
)

Difference Product = 
var tab =  
SUMMARIZE(
    'Table',
    'Table'[Product],
    "Result1",
    CALCULATE(
        SUM('Table'[Sales Value]),
        FILTER(
            ALL('Table'),
            'Table'[Product]=EARLIER('Table'[Product])&&
            MONTH('Table'[Sale Date])=MONTH(TODAY())-1
        )
    ),
    "Result2",
    CALCULATE(
        SUM('Table'[Sales Value]),
        FILTER(
            ALL('Table'),
            'Table'[Product]=EARLIER('Table'[Product])&&
            MONTH('Table'[Sale Date])=MONTH(TODAY())
        )
    )
)
return
SUMX(
    tab,
    [Result2]-[Result1]
)

%Change Product = 
var tab =  
SUMMARIZE(
    'Table',
    'Table'[Product],
    "Result1",
    CALCULATE(
        SUM('Table'[Sales Value]),
        FILTER(
            ALL('Table'),
            'Table'[Product]=EARLIER('Table'[Product])&&
            MONTH('Table'[Sale Date])=MONTH(TODAY())-1
        )
    ),
    "Result2",
    CALCULATE(
        SUM('Table'[Sales Value]),
        FILTER(
            ALL('Table'),
            'Table'[Product]=EARLIER('Table'[Product])&&
            MONTH('Table'[Sale Date])=MONTH(TODAY())
        )
    )
)
return
SUMX(
    tab,
    DIVIDE(
           [Result2]-[Result1],
           [Result1]
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , better to time intelligence with date table.

examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Next month value =  CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))
	


diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

In case you do not have a date, try the same approach as a week , by creating rank on month year

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Greg_Deckler · Accepted solution]

@Anonymous You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.