Forum Discussion
Temp table inside measure
Hi all,
To explain my problem we want to create prediction for EOM conversion rate (CR),
What my boss wants is basically
CR based on (last 3 months by source * ("views /day this month by source" * number of days))/ (Expected views this month)
So CR is not just clicks/ views but weighted average based on source,
If slicer would be on monthly basis, This could be solved pretty easily with calculated table with
source, month, "CR over last 3 months"
however requirements changed and slicer is based on "between days" so it doesn't make sense anymore to have bloated table with so many rows (365 days* 150 sources)
and also there are some other complications that are solved in current measures but it would be hard to be address them in calculated table (filters, relationships...)
For other reasons and measures we have 3 tables for that
Click fact table
| Source | Date | Clicks |
| 11/06/2020 | 16 | |
| 12/06/2020 | 20 | |
| 05/06/2020 | 2 | |
| 06/07/2020 | 1 |
Views fact table
| Source | Date | Views |
| 11/06/2020 | 43 | |
| 12/06/2020 | 55 | |
| 05/06/2020 | 11 | |
| 06/07/2020 | 5 |
Source dim table
| Source |
I hope somebody understands what I tried to explain.
Thanks.
3 Replies
Anonymous , Not ver clear .
Last three month with date table and time intelligence
example
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)) Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))Days in this month
Days in this month = day(eomonth(today(),0) ) Days in this month = day(eomonth(max(Table[Date]),0) )TIll this date
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) MTD Days= CALCULATE(count(Date[Date]),DATESMTD('Date'[Date])) or MTD Days= CALCULATE(distinctcount(Date[Date]),DATESMTD('Date'[Date]))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Sorry, I didn't explain myself properly.
This helps me as well, but my main issue is
as seen on example, "Measure 7" is what I want to have as KPI, overall one.
But as this only aggregates tables, I have issue to get total value correct.
Instead of total leads*CR
I would like to see there 25.6+390.55+691.78+43.99 which is 1152,
Solved!
Seems like my problem was, that I was putting variables outside of SUMX iterator
as explained here https://community.powerbi.com/t5/Desktop/DAX-Using-variables-inside-iterators-such-as-SUMX/td-p/521044
