Weekly Running Total and Transformation Rate based on Two Measures

[Green_Cloud]

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 · Accepted solution]

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.

[v-yalanwu-msft]

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]

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:

[amitchandak]

@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]

Thanks for the response. Can you please elaborate more?