How to initialize curves on the same "0 point" in a date x-axis

[Anonymous]

Hi,

I want to vizualize the refund of products on time, period by period (year in this example, but also per quarter). I have data since 2016.

Here is the measure I used (and I put Date in x-axis and 

rate = 
CALCULATE (
    SUM ( tableSales[amountRefund] ), tableSales[refund]=="YES",
    FILTER (
        ALLSELECTED( tableSales[dateRefund]) ,
         tableSales[dateRefund] <= MAX (  'Date'[Date] )
    )
)/

    SUM ( tableSales[dateRefund][amountInitial)

https://www.zupimages.net/up/20/34/rdjn.png

But I want all curves on the same x-axis : +0, +1, +2... (each step is a month), until +60.

+0 might be 01/01/Year (as you see there are some problems with some dates < 01/01, so i dont want to take them as +0)

Any idea ?

[dedelman_clng]

Hi @Anonymous  - if I understand what you're asking, try this:

Instead of having an absolute date as your axis, computer the number of months that each date is past the start date and use that as your x-axis (So you would have 0, 1, 2, 3, etc).  You can use an identifier on the data (start date, perhaps) as a legend to show the different curves.  Something like this (x-axis is hour of the day, legend is Week number, but its the same idea)

Please share some sample data or an pbix with sensitive data removed if you need further assistance.

Hope this helps

David 

[Anonymous]

Yes but the starting date is not the same for each year

How do you would change the formula to do that ? (i create a specific table "Date" to have continious date in x-axis)

[dedelman_clng]

Hi @Anonymous - Please provide either some of your fact data (not the Date table) in a format that can be copied (not a screen shot) or a link to a copy of your PBIX file so we can take a closer look.

Thanks,

David

[Anonymous]

I cant extract data. You really cant give me some tips considering the formula I have posted ?

[dedelman_clng]

@Anonymous  - 

For simplicity, let's assume your data looks like this

Product ProductLaunchDate

A   1/1/2019

B   5/1/2019

C  10/1/2019

Product  Rebate Date

A    5/1/2019

A    6/1/2019

A   10/1/2019

B   9/1/2019

B   11/1/2019

B  6/1/2020

C   1/1/2020

C   4/1/2020

C   8/1/2020

We are not going to use the date table here, because we are going to turn "Rebate Date" into an Offset from the product Launch Date using a calculated column

Rebate Offset = DATEDIFF(RELATED(Products[ProductLaunchDate]), Rebates[RebateDate], MONTH)

Now we can use similar logic to your measure to calculate the running total of rebates

Rebate Count = CALCULATE(COUNTROWS(Rebates), 
          FILTER(ALL(Rebates), 
          Rebates[Rebate Offset]<=MAX(Rebates[Rebate Offset])))

Use [Rebate Offset] as the X-axis, [Rebate Count] as the Value and [Product] or [Launch Date] as the Legend and...

You can adapt it to your specific data structure, but here are the basics.

Hope this helps

David

[Anonymous]

Thanks. So I am using your formula as follow :

Rebate Count = CALCULATE( SUM ( tableSales[amountRefund] ), tablesSales[Refund]=="YES",
          FILTER(ALL(tableSales), 
          tableSales[Rebate Offset]<=MAX(tableSales[Rebate Offset])))

But I have stacked results, that's strange I dont understand why :

https://www.zupimages.net/up/20/34/lc7i.png

[Anonymous]

I give you also my Rebate Offset formula in my case :

Rebate Offset = DATEDIFF(DATE(tableSales[SalesDate].[Year],1,1),tableSales[dateRefund], MONTH)

[Anonymous]

@dedelman_clng Do you have the pbix of your example ? I tried it and it didnt give me the same results. By the way, when I read your result, you have 8 rows counted for A at the end but only 3 in your example. Did you use more date example than in your example ?

[Anonymous]

@dedelman_clng Here is a screen of what I obtain with your example

[dedelman_clng]

Sorry, I didn't check my formula and result closely enough. Here is what we're expecting in my example:

Rebate Count =
CALCULATE (
    COUNTROWS ( Rebates ),
    FILTER (
        ALLEXCEPT ( Rebates, Products[ProductLaunchDate] ),
        Rebates[Rebate Offset] <= MAX ( Rebates[Rebate Offset] )
    )
)

The legend is coming from the "parent" table (the ALLEXECPT works for either Product or LaunchDate). So your formula should be something like this

Rebate Count = CALCULATE( SUM ( tableSales[amountRefund] ), 
          tablesSales[Refund]=="YES",
          FILTER(ALLEXCEPT(tableSales, SalesDate), 
          tableSales[Rebate Offset]<=MAX(tableSales[Rebate Offset])))

If this still isn't working I'll try to ask someone else for help as it is difficult to debug your particular case without data or a pbix to work from.

Hope this helps

David

[Anonymous]

Oh ok ! I am happy to discover it was a missing thing and not something really strange between your power bi and mine 😛

In my case 

ALLEXCEPT(tableSales, SalesDate)

didnt work

but

ALL(tableSales[Rebate Offset]

 works !

The last thing missing is now to divide by the sum of all "initial value amount" of all item for the same year (or the same product in your case). If I simply add

SUM(tableSales[Amount])

it not seems to divide by the whole year amount.

Any idea ? It's my last problem ^^

[v-deddai1-msft]

Hi @Anonymous ,

Would you please try to use 

CALCULATE(SUM(tableSales[Amount]),FILTER(ALL(tableSales),YEAR(tableSales[dateRefund]) = YEAR(MAX(tableSales[dateRefund]))))

instead of 

SUM(tableSales[Amount])

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai