Forum Discussion

Sedondo's avatar
Sedondo
Regular Visitor
9 years ago
Solved

last year values are missing

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 i...
Reports
  • Sedondo's avatar
    Sedondo
    9 years ago

    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

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

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's avatar
Sedondo
Regular Visitor
9 years ago

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's avatar
    Sedondo
    Regular Visitor
    9 years ago

    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