How to compare apples to apples YTD, when some months are missing sales?

[HappyCamper]

Hello Power BI Friends!

First time poster, long time fan of the advice given! 😄

Several clients that reported sales last year are no longer purchasing this year, so I want to be able to see an adjusted YTD vs. Last Year that accurately removes values of last year if no value is in current year. 

I know its either a sumif, while/then, or a loop formula... but honestly, I have no idea how to build this formula... any help would be greatly appreciated! 

For example:

[smpa01]

@HappyCamper  it is an extremely interesting problem and even though @Ashish_Mathur  provided a great solution, I tried my hand on this as I approached the problem little differently.

A. Firstly I calculated what are the common months between max year in the data (2019 in this case) and non-max year (<2019 in this case).  If there is no sale in max year for those common months by Customer (sold-to), Calendar month, Brand, Line, Subline, SKU then sale=0 in non max year by Customer (sold-to), Calendar month, Brand, Line, Subline, SKU (I tried to attend to the last level of granularity to alter the sale to 0 on the condition specified)

Calendar month

APR

FEB

JAN

JUN

MAR

MAY

AUG

JUL

B. What are the months that are in non max year but not in max year. The correponding values are modified to 0 for non max year rows for Customer (sold-to), Calendar month, Brand, Line, Subline, SKU  as the end goal is to compare apple to apples

Calendar month

DEC

NOV

OCT

SEP

C. Keep the sale unmodified in max year.

I have done this by a DAX query

    Table =
VAR _11 =
    MAX ( 'Raw Data'[Calendar Year] ) ///Calculate Max year///
VAR _121 =
    SUMMARIZECOLUMNS (
        'Raw Data'[Calendar month],
        FILTER ( ALL ( 'Raw Data'[Calendar Year] ), 'Raw Data'[Calendar Year] = _11 ),
        "@Month", DISTINCT ( 'Raw Data'[Calendar month] )
    ) //What are the months in Max Year//
VAR _131 =
    DISTINCT ( SELECTCOLUMNS ( _121, "Calendar month", [@Month] ) ) //What are the months in Max Year//
VAR _141 =
    SUMMARIZECOLUMNS (
        'Raw Data'[Calendar month],
        FILTER ( ALL ( 'Raw Data'[Calendar Year] ), 'Raw Data'[Calendar Year] < _11 ),
        "@Month", DISTINCT ( 'Raw Data'[Calendar month] )
    ) //What are the months in Non-Max Year//
VAR _151 =
    DISTINCT ( SELECTCOLUMNS ( _141, "Calendar month", [@Month] ) ) //What are the months in Non-Max Year//
VAR _161 =
    EXCEPT ( _151, _131 ) // Are there any months that are in Non-Max year but not in max year//
VAR _21 =
    SUMMARIZECOLUMNS (
        'Raw Data'[Customer (sold-to)],
        'Raw Data'[Calendar Year],
        'Raw Data'[Calendar month],
        'Raw Data'[Brand],
        'Raw Data'[Line],
        'Raw Data'[Subline],
        'Raw Data'[SKU],
        FILTER ( ALL ( 'Raw Data'[Calendar Year] ), 'Raw Data'[Calendar Year] < _11 ),
        "@Sales", SUM ( 'Raw Data'[Sell Out Value] )
    ) // Summarize sales by non-max Year//
VAR _t0 =
    NATURALINNERJOIN (
        SELECTCOLUMNS (
            _21,
            "Customer (sold-to)", [Customer (sold-to)],
            "Calendar Year", [Calendar Year],
            "Calendar month", [Calendar month] & "",
            "Brand", [Brand],
            "Line", [Line],
            "Subline", [Subline],
            "Sales", [@Sales],
            "SKU", [SKU] & ""
        ),
        SELECTCOLUMNS ( _161, "Calendar month", [Calendar month] & "" )
    ) // Summarize sales by non-max Year only for _161 months//
VAR _t1 =
    SELECTCOLUMNS (
        _t0,
        "Calendar Year", [Calendar Year],
        "Calendar month", [Calendar month] & "",
        "Customer (sold-to)", [Customer (sold-to)],
        "Brand", [Brand],
        "Line", [Line],
        "Subline", [Subline],
        "SKU", [SKU],
        "Sales", 0
    )
VAR _71 =
    SUMMARIZECOLUMNS (
        'Raw Data'[Customer (sold-to)],
        'Raw Data'[Calendar Year],
        'Raw Data'[Calendar month],
        'Raw Data'[Brand],
        'Raw Data'[Line],
        'Raw Data'[Subline],
        'Raw Data'[SKU],
        FILTER ( ALL ( 'Raw Data'[Calendar Year] ), 'Raw Data'[Calendar Year] = _11 ),
        "@Sales", SUM ( 'Raw Data'[Sell Out Value] )
    ) // Summarize sales by max Year//
VAR _t2 =
    SELECTCOLUMNS (
        _71,
        "Calendar Year", [Calendar Year],
        "Calendar month", [Calendar month] & "",
        "Customer (sold-to)", [Customer (sold-to)],
        "Brand", [Brand],
        "Line", [Line],
        "Subline", [Subline],
        "SKU", [SKU],
        "Sales", [@Sales]
    )
VAR _1711 =
    NATURALINNERJOIN (
        SELECTCOLUMNS (
            _21,
            "Customer (sold-to)", [Customer (sold-to)],
            "Calendar Year", [Calendar Year],
            "Calendar month", [Calendar month] & "",
            "Brand", [Brand],
            "Line", [Line],
            "Subline", [Subline],
            "SKU", [SKU],
            "@Sales", [@Sales]
        ),
        SELECTCOLUMNS ( _131, "Calendar month", [Calendar month] & "" )
    ) // Summarize sales by non-max Year only for common months//
VAR _1712 =
    ADDCOLUMNS (
        _1711,
        "UQID", CONCATENATE (
            [Customer (sold-to)],
            CONCATENATE (
                [Calendar month],
                CONCATENATE (
                    [Brand],
                    CONCATENATE (
                        [Line],
                        CONCATENATE ( [Subline], CONCATENATE ( [SKU], [@Sales] ) )
                    )
                )
            )
        )
    ) //cretae unique identity//
VAR _1713 =
    SUBSTITUTEWITHINDEX ( _1712, "Index", _1711, 0 ) //inex by uqid//
VAR _1714 =
    NATURALLEFTOUTERJOIN ( _1712, _1713 ) //put back index to the original table//
VAR _1715 =
    SELECTCOLUMNS (
        _1714,
        "Customer (sold-to)", [Customer (sold-to)],
        "Calendar month", [Calendar month],
        "Brand", [Brand],
        "Line", [Line],
        "Subline", [Subline],
        "SKU", [SKU]
    ) // selecting few columns from non2019t//
VAR _1716 =
    SELECTCOLUMNS (
        _71,
        "Customer (sold-to)", [Customer (sold-to)],
        "Calendar month", [Calendar month],
        "Brand", [Brand],
        "Line", [Line],
        "Subline", [Subline],
        "SKU", [SKU]
    ) // selecting few columns from max year table//
VAR _1717 =
    EXCEPT ( _1715, _1716 ) //which are in non-max year but not in max year//
VAR _1718 =
    SELECTCOLUMNS (
        _1714,
        "Index", [Index],
        "Calendar Year", [Calendar Year],
        "Customer (sold-to)", [Customer (sold-to)],
        "Calendar month", [Calendar month],
        "Brand", [Brand],
        "Line", [Line],
        "Subline", [Subline],
        "SKU", [SKU]
    ) // selecting few columns from non2019t//
VAR _1719 =
    NATURALINNERJOIN ( _1717, _1718 )
VAR _t3 =
    SELECTCOLUMNS (
        _1719,
        "Calendar Year", [Calendar Year],
        "Calendar month", [Calendar month] & "",
        "Customer (sold-to)", [Customer (sold-to)],
        "Brand", [Brand],
        "Line", [Line],
        "Subline", [Subline],
        "SKU", [SKU],
        "Sales", 0
    )
VAR _1720 =
    EXCEPT (
        SELECTCOLUMNS ( _1713, "Index", [Index] + 0 ),
        SELECTCOLUMNS ( _1719, "Index", [Index] + 0 )
    )
VAR _1721 =
    NATURALINNERJOIN ( _1720, _1714 )
VAR _t4 =
    SELECTCOLUMNS (
        _1721,
        "Calendar Year", [Calendar Year],
        "Calendar month", [Calendar month] & "",
        "Customer (sold-to)", [Customer (sold-to)],
        "Brand", [Brand],
        "Line", [Line],
        "Subline", [Subline],
        "SKU", [SKU],
        "Sales", [@Sales]
    )
VAR _FinalTable =
    UNION ( _t1, _t2, _t3, _t4 )
RETURN
    _FinalTable

High_Level

Granular

[HappyCamper]

Hello @Ashish_Mathur  & @smpa01 & @amitchandak & @Grambi ,

Thank you both so much! I am truly grateful for your support on this matter! I am sincerely impressed with this community and it makes me want to become a Power BI Pro. 😄

@smpa01 , yes, you are exactly right, your tables are exactly what I need. I have created the table in my power BI, however I am now getting the error "The expression refers to mulitple columns. Multiple columns cannot be converted to a scalar value." Any ideas?

Are you able to share the power bi you built with this data?

Thank you all again!

[smpa01]

@HappyCamper  I am not sure what causes that but the DAX query is working absolutely fine on my end. You need to create a new table in order this query to run

[Ashish_Mathur]

Hi,

Please clearly explain how you arrived at the numbers in the green box of the second image?  Share the logic very clearly.  Also, share data in a format that i can paste in an MS Excel workbook.

[HappyCamper]

Hello @Ashish_Mathur 

Thank you in advance for your support on this! I sincerely appreciate it! 😄 

So, I am looking to get the adjusted sales comparison. Meaning, if the current month's sales are zero, than I want last year's month sales to equal 0 (Even if there are sales in the LY sales)

The bottom table was showing how I wanted to calculate the adjusted YTD 

As you can see, in 2019, clients 1 and 2 are missings sales for a few months. So, I changed last year's month sales to 0 to match 2019. 

Does this make sense?

	2018								2019
	Jan	Feb	Mar	Apr	May	Jun	Jul		Jan	Feb	Mar	Apr	May	Jun	Jul
Client 1	9,935	7,657	8,718	9,571	12,543	13,159	6,811		10,196	21,800	31,438	38,873	0	0	0
Client 2	7,534	11,377	18,446	11,685	14,497	12,158	14,367		14,608	10,862	0	0	0	0	0
Client 3	42,141	43,738	30,490	33,566	42,277	34,993	23,446		32,599	26,414	25,251	23,348	40,451	35,209	23,878
Client 4	8,958	8,546	7,772	9,021	10,964	7,951	8,490		7,637	6,410	5,809	7,459	3,821	4,945	2,238
Client 5	22,029	15,157	16,027	18,375	21,551	16,228	18,227		19,581	15,305	14,721	27,919	10,621	12,859	12,293
Client 6	20,528	14,824	34,221	20,111	22,176	18,840	17,841		11,720	15,699	17,271	30,297	29,999	19,257	22,789
Client 7	22,267	23,646	18,431	26,657	37,551	43,268	22,984		19,073	23,987	21,281	26,609	23,812	26,261	21,837
Total	133,392	124,944	134,105	128,985	161,559	146,597	112,166		115,414	120,477	115,771	154,505	108,704	98,531	83,035
	Current Formula
	YTD 2018	YTD 2019	% ∆
	941,748	796,437	-15%
	2018								2019
	Jan	Feb	Mar	Apr	May	Jun	Jul		Jan	Feb	Mar	Apr	May	Jun	Jul
Client 1	9,935	7,657	8,718	9,571	0	0	0		10,196	21,800	31,438	38,873	0	0	0
Client 2	7,534	11,377	0	0	0	0	0		14,608	10,862	0	0	0	0	0
Client 3	42,141	43,738	30,490	33,566	42,277	34,993	23,446		32,599	26,414	25,251	23,348	40,451	35,209	23,878
Client 4	8,958	8,546	7,772	9,021	10,964	7,951	8,490		7,637	6,410	5,809	7,459	3,821	4,945	2,238
Client 5	22,029	15,157	16,027	18,375	21,551	16,228	18,227		19,581	15,305	14,721	27,919	10,621	12,859	12,293
Client 6	20,528	14,824	34,221	20,111	22,176	18,840	17,841		11,720	15,699	17,271	30,297	29,999	19,257	22,789
Client 7	22,267	23,646	18,431	26,657	37,551	43,268	22,984		19,073	23,987	21,281	26,609	23,812	26,261	21,837
Total	133,392	124,944	115,659	117,300	134,519	121,280	90,988		115,414	120,477	115,771	154,505	108,704	98,531	83,035
	Formula I want
	YTD 2018	YTD 2019	% ∆
	838,082	796,437	-5%

[amitchandak]

Please find the solution in pbix

https://www.dropbox.com/s/hm8phcckyxcolq2/MissingmonthAdjustment.pbix?dl=0

Sales YTD on LYTD = 
Var   _start_date=(minx('Date',STARTOFYEAR('Date'[Date]))) 
Var   _end_date=(max('Date'[Date]))

Var _last_year_mtd_val= CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date] >= _start_date && (Sales[Sales Date]) <= _end_date,filter(Sales,COUNTROWS(SAMEPERIODLASTYEAR(Sales[Sales Date]))>0))
return
_last_year_mtd_val

[amitchandak]

---

@amitchandak wrote:

Please find the solution in pbix

https://www.dropbox.com/s/hm8phcckyxcolq2/MissingmonthAdjustment.pbix?dl=0

Sales YTD on LYTD = 
Var   _start_date=(minx('Date',STARTOFYEAR('Date'[Date]))) 
Var   _end_date=(max('Date'[Date]))

Var _last_year_mtd_val= CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date] >= _start_date && (Sales[Sales Date]) <= _end_date,filter(Sales,COUNTROWS(SAMEPERIODLASTYEAR(Sales[Sales Date]))>0))
return
_last_year_mtd_val

---

@HappyCamper, this solution did not work out ?

[Ashish_Mathur]

Hi,

Share data in a form that i can paste in an Excel workbook.

[HappyCamper]

https://app.box.com/s/fsyu5yih1x0sslr1yiqq3hb6gob46u8y

Hello @Ashish_Mathur ,

Please let me know if you can open this file from my box account.

Thank you!

[Ashish_Mathur]

I understand what you want. Now please share the format in which your actual raw data is arranged.

[HappyCamper]

Hello @Ashish_Mathur ,

Below is the link to my box.com raw data file.

The columns include: Customer, Year, Month, Brand, Line, Subline, SKU, and Sales Value.

This is how the data is pulled from the system.  

https://app.box.com/s/sn32fmn25iestqcfuzx8bg54848fjzl1

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Grambi]

Thanks for teaching that calculation based on SUMMARIZE @Ashish_Mathur . Very insightful

With a little change it's possible to arrange the table visual so it kind of reflects the examples given by HappyCamper, and shows per client and month what part of the sales from last year is or isn't adjusted.

I would need to change the measures Sales this year and Sales last year, so they can be displayed per month. Or add new ones.

sumx(filter('Raw Data';year('Raw Data'[Date])=year(today()));'Raw Data'[Sell Out Value])
sumx(filter('Raw Data';year('Raw Data'[Date])=year(today())-1);'Raw Data'[Sell Out Value])

To go from the default table in your pbix to the one shown above:

• Remove the field Year from the columns
• Add Sales this year, Sales last year, Adjusted sales and Growth to the Values, remove Sell Out from the values.

[Ashish_Mathur]

You are welcome @Grambi 

[amitchandak]

In year current year calc you have to add a filter for last year sales. Something like this

Sales YTD = 
Var   _start_date=year(max('Date'[Date Filer]))
Var   _end_date=year(min(STARTOFYEAR('Date'[Date Filer])))

Var _last_year_mtd_val= CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date] >= _start_date && (Sales[Sales Date]) <= _end_date, filter(Sales,CALCULATE(sum(Sales[Sales Amount]),SAMEPERIODLASTYEAR(Sales[Sales Date]))>0))
return
_last_year_mtd_val

[amitchandak]

In year current year calc you have to add a filter for last year sales. Something like this

Sales YTD = 
Var   _start_date=year(max('Date'[Date Filer]))
Var   _end_date=year(min(STARTOFYEAR('Date'[Date Filer])))

Var _last_year_mtd_val= CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date] >= _start_date && (Sales[Sales Date]) <= _end_date, filter(Sales,CALCULATE(sum(Sales[Sales Amount]),SAMEPERIODLASTYEAR(Sales[Sales Date]))>0))
return
_last_year_mtd_val

[HappyCamper]

Hello @amitchandak! 

Thank you so much for all the support! I am going to work on the file now, I will let you know.

But I sincerely appreciate your reply! 😄