topic Re: Weekly Running Total and Transformation Rate based on Two Measures in DAX Commands and Tips

Weekly Running Total and Transformation Rate based on Two Measures

Green_Cloud — Sun, 01 May 2022 17:04:51 GMT

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:

Re: Weekly Running Total and Transformation Rate based on Two Measures

amitchandak — Mon, 02 May 2022 03:04:18 GMT

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

Re: Weekly Running Total and Transformation Rate based on Two Measures

Green_Cloud — Tue, 03 May 2022 12:49:01 GMT

Thanks for the response. Can you please elaborate more?

Re: Weekly Running Total and Transformation Rate based on Two Measures

v-yalanwu-msft — Wed, 04 May 2022 02:14:36 GMT

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.

Re: Weekly Running Total and Transformation Rate based on Two Measures

Green_Cloud — Wed, 04 May 2022 13:16:11 GMT

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:

Re: Weekly Running Total and Transformation Rate based on Two Measures

v-yalanwu-msft — Thu, 05 May 2022 02:27:17 GMT

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.