Calculate Qty Sold Month by Month by account/product/colour/price

[Intech]

Quantity Shipped Last 6 Months- Month by Month.zip (4.0 MB)
Hi,

We are trying to show qty sold month by month by account/product/colour/price in a table.

We are using:

Current Month =
CALCULATE(
SUM(Sales[Quantity]),
PARALLELPERIOD(Dates[Date], -0,MONTH)) -
CALCULATE(
SUM(Sales[Quantity]),
PARALLELPERIOD(Dates[Date], -1,MONTH))

One Month Ago=
CALCULATE(
SUM(Sales[Quantity]),
PARALLELPERIOD(Dates[Date], -1,MONTH)) -
CALCULATE(
SUM(Sales[Quantity]),
PARALLELPERIOD(Dates[Date], -2,MONTH))

and so on up until six months.

We get the totals right for all the months, but the values displayed within the table (row level) are not. What are we doing wrong? What are we missing on?

Current Month - Expected Result

Current month in PBI

Help would be much appreciated.

Attached PBIX

[JarrettM]

I will have a look at your PBIX and be back in touch. The Totals are correct, but not on the row level?

Thanks
Jarrett

[JarrettM]

Total of the very bottom # of $451.40 is incorrect for Current, correct? You will definitely need to use a measure that has SUMX and Summarize in it to correct the totals. Same has done a video here to help correct measure totals.

Fixing Complex Total Errors - DAX Formula Concepts

Thanks
Jarrett

[Intech]

Thank you Jarrett for looking into this.

The totals are correct for current, one month, two months and so on. All the totals are correct, but they are not on the row level.

[Intech]

Yes, the totals are correct, but not on the row level.

Experts, please need your help here.

[JarrettM]

This is not as simple as it may appear. One issue is the relationship between your Sales & Dates Table. There is no reason for it to be both. Best practice is Single. Here are steps to solve.

1. Create Measure For Days Since Confirmed-

    Days Since Confirmed Date =
    VAR LastConfirmedDate =
        LASTDATE ( Sales[Confirmed Date] )
    RETURN
        DATEDIFF ( LastConfirmedDate, TODAY (), DAY )
   
    Then Create measures for each of your standards. Here is example for current:
    Current =
    CALCULATE (
        [Total Quantity],
        FILTER ( Sales, [Days Since Confirmed Date] < 30 )
    )
   

Here is example for 30:

30 Days JRM =
CALCULATE (
    [Total Quantity],
    FILTER (
        Sales,
        [Days Since Confirmed Date] > 29
            && [Days Since Confirmed Date] <= 59
    )
)

Here is what table looks like after.

Thanks
Jarrett

[Intech]

Hi Jarrett,

Thank you very much for the solution. This saved us so much of hassle and time.

Thank You again

Desmond
Intech

[Intech]

Hi Jarrett,

Thanks for the help again. We have noticed that the dates overlap. The date for the current month shows Feb dates within the same column for March period, same with Feb month showing Jan dates. What are we missing here? Any input would be much appreciated.

Attached pbix file Quantity Shipped Last 6 Months- Month by Month.zip (3.4 MB)

Thanks for the help.

Regards,
Intech

[JarrettM]

Sorry for the late response, but I have been out of the office at a conference all week. Just returning today, and will have a look at this over the weekend.

Thanks
Jarrett

[JarrettM]

Sorry for so late getting back to you, been crazy busy since getting back from the conference last week. Having a look at your file and the reason why those dates are showing in current month is In the measure for current month it is including anything with a confirmed date < 30 days. If this is not how you want to calculate this, then let me know how you would like it calculated.

Thanks
Jarrett

[Intech]

Thanks for getting back to me on this. We want it to calculate for individual months for the last six months, i.e, qty sold in March, Feb, Jan, Dec, Nov, Oct. When April comes Oct month should drop off. Hope it can be achieved.

Thanks for the help again.
Desmond
Intech

[JarrettM]

Here is a quick solution that worked great for me. Here is what it looks like

I added to slicers at the top right, MonthOfYear & Year.
**Whatever Month & Year you have selected with show as CurrentMonth in the table, FYI.

Here are measures I created:

CurrentMonth = 
CALCULATE (
    [Total Quantity],
    FILTER ( Dates, MONTH ( Dates[Date] ) = MONTH ( TODAY () ) ),
    FILTER ( Dates, YEAR ( Dates[Date] ) = YEAR ( TODAY () ) )
)

Last Month = 
CALCULATE ( [Total Quantity], DATEADD ( Dates[Date], -1, MONTH ) )

** Months 2, 3, 4 & 5- All you have to do is copy same measure for Last Month, just change the -1 to -2, -3, -4 & -5 for the different measures.**

Let me know if you have any questions

Thanks
Jarrett

[JarrettM]

Here is the PBIX file so you can see what I did as well. I have some other measures in it while I was testing out other ideas.

Cheers
JarrettQuantity Shipped Last 6 Months- Month by Month (2).zip (3.4 MB)

[Intech]

Hi Jarrett,

Apology for the delay. Have tested it and I am getting the expected result.

Thank You again for the help. Much appreciated.

Cheers,
Desmond
Intech