Forum Discussion

axel_BI's avatar
axel_BI
Icon for Helper I rankHelper I
6 years ago
Solved

Comparing day to day

Hello everybody,

 

following case:

 

I have the data of multiple Point of Sales. Each day, they get delivered a variety of articles. Let's say newspapers. I know which POS receives which amount of every Article.

On the following day, they receive the new newspaper and the old ones (from the day before which are not sold) are thrown away. This happens every day from Monday to Saturday. So Monday throws away the articles from Saturday, not Sunday.

 

My goal is, to give an overwiew of the ratio "waste" versus "delivered", so that we can optimize wich article should be placed in which POS with which amount. But i cant even get to display the waste of the nextday with the delivered in one table.

In a Table, with the original Date-Column i tried to use following formula. This works only if dont filter on POS or Article

 

Sum Waste Following Day = CALCULATE([Sum Waste];DATEADD(Transaktions[Date];1;DAY))
 
Also i wanted to use second calendertable for date hierarchy etc. If i use above formula with calendertable[Date] nothing works as well.
To give you a rough understanding how the data is coming through.
 
DateShopIDArticleAmountTransaktion
01.05.2020A123Newspaper13delivered
01.05.2020B456Newspaper21delivered
02.05.2020A123Newspaper11waste
02.05.2020B456Newspaper21waste
02.05.2020A123Newspaper21delivered
02.05.2020A123Newspaper11delivered
03.05.2020B456Newspaper11delivered
03.05.2020A123Newspaper11waste
03.05.2020B456Newspaper10waste

 

Help is much appreciated.

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi axel_BI ,

     

    We can use the following steps to meet your requirement.

     

    1. Create a calendar table. And create a relationship between two tables.

     

    calendertable = CALENDAR("2020/5/1","2020/6/30")

     

     

     

    2. We can create four measures.

     

    sum_delivered = CALCULATE(SUM('Transaktions'[Amount]),FILTER('Transaktions','Transaktions'[Transaktion]="delivered"))

     

    sum_waste = 
CALCULATE(SUM('Transaktions'[Amount]),FILTER(Transaktions,'Transaktions'[Transaktion]="waste" ))

     

    Sum Waste Following Day = 
var current_ = MAX(calendertable[Date])+1
return
CALCULATE([sum_waste],FILTER(ALL(calendertable),calendertable[Date]=current_))

     

    Waste % = DIVIDE([Sum Waste Following Day],[sum_delivered],0)

     

    Then we can create a table visual based on the fields in the screenshot.

     

     

    3. At last we can create three slicers based on calendertable[Date], [shopID] and [Article].

     

     

     

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

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

3 Replies

    • axel_BI's avatar
      axel_BI
      Icon for Helper I rankHelper I
      6 years ago

      Hi Greg,
      Something like this

      DateShopIDArticleAmountTransaktionWaste nextdayWaste % 
      01.05.2020A123Newspaper13delivered133,33%
      01.05.2020B456Newspaper21delivered1100,00%
      02.05.2020A123Newspaper21delivered00,00%
      02.05.2020A123Newspaper11delivered1100,00%
      03.05.2020B456Newspaper11delivered00,00%


      The dream would be to have it aggregated and be abe to drill down on "perDay, per Shop, per Product"
      My above formula can only do total delivered, total waste nextday, differentiated by date....if i filter or add hierarchies it stops working


  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi axel_BI ,

     

    We can use the following steps to meet your requirement.

     

    1. Create a calendar table. And create a relationship between two tables.

     

    calendertable = CALENDAR("2020/5/1","2020/6/30")

     

     

     

    2. We can create four measures.

     

    sum_delivered = CALCULATE(SUM('Transaktions'[Amount]),FILTER('Transaktions','Transaktions'[Transaktion]="delivered"))

     

    sum_waste = 
CALCULATE(SUM('Transaktions'[Amount]),FILTER(Transaktions,'Transaktions'[Transaktion]="waste" ))

     

    Sum Waste Following Day = 
var current_ = MAX(calendertable[Date])+1
return
CALCULATE([sum_waste],FILTER(ALL(calendertable),calendertable[Date]=current_))

     

    Waste % = DIVIDE([Sum Waste Following Day],[sum_delivered],0)

     

    Then we can create a table visual based on the fields in the screenshot.

     

     

    3. At last we can create three slicers based on calendertable[Date], [shopID] and [Article].

     

     

     

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

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