Forum Discussion

Green_Cloud's avatar
Green_Cloud
Icon for Helper I rankHelper I
4 years ago
Solved

Weekly Running Total and Transformation Rate based on Two Measures

From the data source below, how can I build a report given that-

Ad count=COUNT (adddate)

Sales count= COUNT(salesdate)

 

Report and Data Source are below-

REPORT:

 

DATA SOURCE:

 

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Green_Cloud ;

    Change it.

    countstart = CALCULATE(COUNT('Table'[unique id]),FILTER('Table',[addate]<=MAX('Date'[weekend])))
    countend = CALCULATE(COUNT('Table'[unique id]),FILTER('Table',[saledate]<=MAX('Date'[weekend])))
    rate = [countend]/[countstart]

    The final output is shown below:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Green_Cloud , With help from the date table, joined to date or to week date of your table.

     

    You can have the week start and weekend date on your table

     

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

     

     

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date] <=max(date[Date])))

    • Green_Cloud's avatar
      Green_Cloud
      Icon for Helper I rankHelper I
      4 years ago

      Thanks for the response. Can you please elaborate more?

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Green_Cloud ;

    1.create a table.

    Date = 
ADDCOLUMNS( FILTER( CALENDAR(DATE(2021,12,31),TODAY()) ,WEEKDAY([Date]+2,1) =1),"weeknum",WEEKNUM([Date]+2,1)-1,"weekend",[Date]+6 )

    2.create measures.

    countstart = CALCULATE(COUNT('Table'[unique id]),FILTER('Table',[addate]<=MAX('Date'[weekstart])))
    countend = CALCULATE(COUNT('Table'[unique id]),FILTER('Table',[saledate]<=MAX('Date'[weekend])))

    The final output is shown below:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Green_Cloud's avatar
      Green_Cloud
      Icon for Helper I rankHelper I
      4 years ago

      Thanks for the response. But, the expected output is as follows as we need to find the cummulative number of both measures (adcount and sales count) and corresponding transformation rate (salescount/ adcount) each week:

       

       

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Green_Cloud ;

    Change it.

    countstart = CALCULATE(COUNT('Table'[unique id]),FILTER('Table',[addate]<=MAX('Date'[weekend])))
    countend = CALCULATE(COUNT('Table'[unique id]),FILTER('Table',[saledate]<=MAX('Date'[weekend])))
    rate = [countend]/[countstart]

    The final output is shown below:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.