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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How do I insert month into my PY sales formula so it shows PY sales by month on the chart?

Dear All,

I really need your help.

I need to calculate Sales for current year (POS CY), for last year and two years ago (my dates are not continuous, thus I can’t use DATESADD etc).
Those are my formulas:
POS CY =

CALCULATE (

    Sales[POS],

    FILTER (Sales, YEAR ( 'Sales'[Week_Ending] ) = MAX ( 'Sales'[Year] ) )

)


POS CY =

CALCULATE (

    Sales[POS],

    FILTER (Sales, YEAR ( 'Sales'[Week_Ending] ) = MAX ( 'Sales'[Year] ) )

)


POS 2YRS AGO Alternative =

CALCULATE (

    Sales[POS],

    FILTER (

        'Sales',

        YEAR ( 'Sales'[Week_Ending] )

            = MAX ( 'Sales'[Year] ) - 2

    )

)


So, the results are fine when I use the table visual, however I run into problem when I try to display sales on a clustered column chart. The sales for Jan and Feb show the corrects results for three years, whereas the sales for March 2019 to Dec 2019 – are wrong – the 2018 sales shift to 2019 sales and 2017 sales (2 Yr Ago sales) are shown as last year sales.

To prove my point, I hardcoded the sales by year and have two charts - the top has the dynamic formulas that shift starting in March and the bottom chart shows the right results

How do I insert month into my formula so it shows PY sales and sales 2 yrs ago correctly on the chart?

Capture.PNG

1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous,

 

By my test based on your description, you could create a calendar table with CALENDARAUTO().

 

Then you could modify your formula like below.

 

CY =
CALCULATE (
    SUM ( Sales[Value] ),
    FILTER (
        'Sales',
        YEAR ( 'Sales'[Date] ) = YEAR ( MAX ( 'Sales'[Date] ) )
            && MONTH ( 'Sales'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
    )
)

PY =
CALCULATE (
    SUM ( Sales[Value] ),
    FILTER (
        'Sales',
        YEAR ( 'Sales'[Date] )
            = YEAR ( MAX ( 'Sales'[Date] ) ) - 1
            && MONTH ( 'Sales'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
    )
)

L2Y =
CALCULATE (
    SUM ( Sales[Value] ),
    FILTER (
        'Sales',
        YEAR ( 'Sales'[Date] )
            = YEAR ( MAX ( 'Sales'[Date] ) ) - 2
            && MONTH ( 'Sales'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
    )
)

Then you could create the chart like below.

 

Capture.PNG

 

More detaild, please refer to the attachment.

 

If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.)

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Dear All,

I really need your help.

I need to calculate Sales for current year (POS CY), for last year and two years ago (my dates are not continuous, thus I can’t use DATESADD etc).
Those are my formulas:
POS CY =

CALCULATE (

    Sales[POS],

    FILTER (Sales, YEAR ( 'Sales'[Week_Ending] ) = MAX ( 'Sales'[Year] ) )

)


POS CY =

CALCULATE (

    Sales[POS],

    FILTER (Sales, YEAR ( 'Sales'[Week_Ending] ) = MAX ( 'Sales'[Year] ) )

)


POS 2YRS AGO Alternative =

CALCULATE (

    Sales[POS],

    FILTER (

        'Sales',

        YEAR ( 'Sales'[Week_Ending] )

            = MAX ( 'Sales'[Year] ) - 2

    )

)


So, the results are fine when I use the table visual, however I run into problem when I try to display sales on a clustered column chart. The sales for Jan and Feb show the corrects results for three years, whereas the sales for March 2019 to Dec 2019 – are wrong – the 2018 sales shift to 2019 sales and 2017 sales (2 Yr Ago sales) are shown as last year sales.

To prove my point, I hardcoded the sales by year and have two charts - the top has the dynamic formulas that shift starting in March and the bottom chart shows the right results

How do I insert month into my formula so it shows correctly on the chart?

Capture.PNG



Anonymous
Not applicable

Post this in the Power BI community. This forum is for bug reporting. You will get more help in the PBI community forum. There might already be an answer there for you...

v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous,

 

By my test based on your description, you could create a calendar table with CALENDARAUTO().

 

Then you could modify your formula like below.

 

CY =
CALCULATE (
    SUM ( Sales[Value] ),
    FILTER (
        'Sales',
        YEAR ( 'Sales'[Date] ) = YEAR ( MAX ( 'Sales'[Date] ) )
            && MONTH ( 'Sales'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
    )
)

PY =
CALCULATE (
    SUM ( Sales[Value] ),
    FILTER (
        'Sales',
        YEAR ( 'Sales'[Date] )
            = YEAR ( MAX ( 'Sales'[Date] ) ) - 1
            && MONTH ( 'Sales'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
    )
)

L2Y =
CALCULATE (
    SUM ( Sales[Value] ),
    FILTER (
        'Sales',
        YEAR ( 'Sales'[Date] )
            = YEAR ( MAX ( 'Sales'[Date] ) ) - 2
            && MONTH ( 'Sales'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
    )
)

Then you could create the chart like below.

 

Capture.PNG

 

More detaild, please refer to the attachment.

 

If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.)

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Dear Cherry,

thank you for your input.

I have a calendar table in my power bi report that has data till 2019-12-31 so when I tried creating an auto calendar it got all the dates till the end of Dec.

I tried then pulling the sales calendar (as it has dates till February) but now I have two calendar tables and I am worried about connecting my Sales table to a newly created POS Calendar as I don't know how to overcome the issue of POS table connecting to two Calendar tables (or picking between them).

Is there a way to calculate POS PY without creating a new table? 
Could I somehow specify in my POS PY calculation that I need to pull the latest month from the latest year

POS PY= CALCULATE(Sales[POS],FILTER('Sales',YEAR('Sales'[Week_Ending])=MAX('Sales'[Year])-1) &&... the latest month from the latest year )

I tried writing [original formula] && Sales[Row Number] = MAX(Sales[Row Number] ))) but then my POS PY calculation would disappear.

I don't know why and I have no idea how to fix my calculation

Capture 2.PNG

Hi @Anonymous,

 

If you have had a calendar table, you don't need to create a new one with CALENDARAUTO().

 

You could see from my attachment, my calendar table also have date till 2019-12-31.

 

Please note that I did not create the relationship between the calendar table and the Sales table in my attachment.

 

If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.)

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.