last year values are missing

[Sedondo]

Hi,

I want to compare the sales of our customers to the last year sales.

I just realized, that all the customers, who had sales in last year but do not have sales in this year are not shown in my table.

I do not use a filter.

this is my calculation:

Sales this year = CALCULATE(sum(customerinv[invoicesum]);DATEADD(DATESYTD(Date[Date]);0;YEAR))

Sales last year = CALCULATE(sum(customerinv[invoicesum]);DATEADD(DATESYTD(Date[Date]);-1;YEAR))

Thanks for your support in advance

Sedat

[v-haibl-msft]

@Sedondo

I'm not sure what does your table like and which columns and values do you want to show in the Table visual. I just test with a simple sample like below.

If the result is not what you desired, please post some sample data and the expected visual to us.

Best Regards,
Herbert

[Sedondo]

Dear Herbert,

I just created a sample data with the same problem.

https://www.dropbox.com/s/h7027l2bwfjo7ip/sample%20data.pbix?dl=0

If you compare the 2016 report with the 2017 report, you will see that customer D is missing in 2017.

That has something to do with the connection of the date table and the sales table. If you change the filter direction from both to one, you get the right result.

I will try if I will achive anyting with a similar change in my database.

PS: It is not really working - now the dateadd - datesytd fucntion is not working adaquate - now 2016 in total is compared to cumulated May 2017.

[v-haibl-msft]

@Sedondo

Changing the filter direction from both to one should be the easier one.

You can also try to create a customer table with following DAX formula and create single relationship with 'customerinv'.

CustomerTable =
VALUES ( customerinv[customer] )

Create a Year column in 'customerinv'.

Year = 
YEAR( customerinv[Date] )

Then create two measures like below.

Sales this year_2 = 
VAR SelectedYear =
    IF ( HASONEFILTER ( Tabelle2[Jahr] ), CALCULATE ( MAX ( Tabelle2[Jahr] ) ) )
RETURN
    IF (
        HASONEFILTER ( Tabelle2[Jahr] ),
        CALCULATE (
            SUM ( customerinv[Invoice] ),
            FILTER ( customerinv, customerinv[Year] = SelectedYear )
        ),
        BLANK ()
    )

Sales last year_2 = 
VAR SelectedYear =
    IF (
        HASONEFILTER ( Tabelle2[Jahr] ),
        CALCULATE ( MAX ( Tabelle2[Jahr] ), ALLSELECTED ( customerinv ) )
    )
RETURN
    CALCULATE (
        SUM ( customerinv[Invoice] ),
        FILTER ( ALL ( customerinv ), customerinv[Year] = SelectedYear - 1 ),
        VALUES ( CustomerTable[customer] )
    )

Best Regards,
Herbert

[Sedondo]

Dear Herbert,

Thanks for your efforts.

My original database ist quite complex and I need repors for customers, customer groups, locations, products, product groups and so on.

So I was hoping that there is a solution without creating additional tables.

What I do not understand is, that the functions DATEADD and DATESYTD (this one is really essential) are not working (why are those results, where I do have sales in the previous year but not this year, are not shown?)

And I prefer the DATESYTD solution, because I also need results of single month, where I use the DATESMTD function.

Thanks again

Sedat

[Sedondo · Accepted solution]

I found a solution which works fine for me.

I changed the filter direction from both to one.

I set a report filter, where I filter the date from the oldest date (in my case 1/1/2010) to the current date (this one i change after every update).

Now, everything is working fine.

Thank you for your support

[Vvelarde]

@Sedondo

Hi, try with this:

Sales last year = CALCULATE(sum(customerinv[Invoice]);PREVIOUSYEAR(Tabelle2[Datum]))

Sales this year = CALCULATE(sum(customerinv[Invoice]))