Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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

HarshaTNH_0-1597384547906.png

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
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

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

Table:

a1.png

 

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:

a2.png

 

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
Super User
Super User

@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
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.