Temp table inside measure

[Anonymous]

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
Facebook	11/06/2020	16
Facebook	12/06/2020	20
Reddit	05/06/2020	2
Twitter	06/07/2020	1

Views fact table

Source	Date	Views
Facebook	11/06/2020	43
Facebook	12/06/2020	55
Reddit	05/06/2020	11
Twitter	06/07/2020	5

Source dim table

Source

Facebook

Facebook

Reddit

Twitter

I hope somebody understands what I tried to explain. 

Thanks. 

[amitchandak]

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

[Anonymous]

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, 

[Anonymous]

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/5210...