Measure: Projection based on max date while establishing/maint a date relationship in another column

[rtaylor]

Hello and Thanks for your Help!,

Sample Report https://drive.google.com/a/mail.sdsu.edu/file/d/1TzC1ljUvrejDgtxjkiP6K1EWFnodrv45/view?usp=sharing

I'm trying to create a measure that calculations an amount but filters based on the max date of one column, and establishes the date relationship through another column.

Please see and example of the table

Date Entry	Date Month	Amount
1/15/2019	7/1/2019	31,564.00
1/15/2019	8/1/2019	646,512.00
1/15/2019	9/1/2019	1,261,460.00
1/15/2019	10/1/2019	1,876,408.00
1/15/2019	11/1/2019	2,491,356.00
1/15/2019	12/1/2019	3,106,304.00
1/15/2019	1/1/2020	3,721,252.00
1/15/2019	2/1/2020	4,336,200.00
1/15/2019	3/1/2020	4,951,148.00
1/15/2019	4/1/2020	5,566,096.00
1/15/2019	5/1/2020	6,181,044.00
1/15/2019	6/1/2020	6,795,992.00
3/15/2019	7/1/2019	30,010.00
3/15/2019	8/1/2019	644,958.00
3/15/2019	9/1/2019	1,259,906.00
3/15/2019	10/1/2019	1,874,854.00
3/15/2019	11/1/2019	2,489,802.00
3/15/2019	12/1/2019	3,104,750.00
3/15/2019	1/1/2020	3,719,698.00
3/15/2019	2/1/2020	4,334,646.00
3/15/2019	3/1/2020	4,949,594.00
3/15/2019	4/1/2020	5,564,542.00
3/15/2019	5/1/2020	6,179,490.00
3/15/2019	6/1/2020	6,794,438.00
4/15/2019	7/1/2019	84,664.00
4/15/2019	8/1/2019	699,612.00
4/15/2019	9/1/2019	1,314,560.00
4/15/2019	10/1/2019	1,929,508.00
4/15/2019	11/1/2019	2,544,456.00
4/15/2019	12/1/2019	3,159,404.00
4/15/2019	1/1/2020	3,774,352.00
4/15/2019	2/1/2020	4,389,300.00
4/15/2019	3/1/2020	5,004,248.00
4/15/2019	4/1/2020	5,619,196.00
4/15/2019	5/1/2020	6,234,144.00
4/15/2019	6/1/2020	6,849,092.00

Process:

If a user were to select March as filter, the following data would be calculated. I would be able to plot this data on graph if needed.

Date Entry	Date Month: Date Relationship	Projection
3/15/2019	7/1/2019	30,010.00
3/15/2019	8/1/2019	644,958.00
3/15/2019	9/1/2019	1,259,906.00
3/15/2019	10/1/2019	1,874,854.00
3/15/2019	11/1/2019	2,489,802.00
3/15/2019	12/1/2019	3,104,750.00
3/15/2019	1/1/2020	3,719,698.00
3/15/2019	2/1/2020	4,334,646.00
3/15/2019	3/1/2020	4,949,594.00
3/15/2019	4/1/2020	5,564,542.00
3/15/2019	5/1/2020	6,179,490.00
3/15/2019	6/1/2020	6,794,438.00

I've go some part of the measures created so far, but I can't seem to be establish/view the date and date table relationship.

Projection1 =

Calculate(sum('Projection_Adaptive'[Projection]),Filter(Projection_Adaptive,Projection_Adaptive[Date Entry]<=max('Date'[Date])&&Projection_Adaptive[Date Entry]>=min('Date'[Date])))

Projection2 = CALCULATE([Projection1],CROSSFILTER('Projection_Adaptive'[DateMonth],'Date'[Date],OneWay))

Any help would be greatly appreciated

[v-frfei-msft]

Hi @rtaylor ,

We can create relationship between date table and the fact table like that.

Based on that, USERELATIONSHIP can help you in your scenario.

Measure = CALCULATE(SUM('Table'[ Amount]),USERELATIONSHIP('date'[Date],'Table'[Date Month]))

[rtaylor]

Hi Thanks for the response.

I thought Cross Filter did the same thing?

Anyways I've tried both and still get the same answer. Please see below a sample model report

https://drive.google.com/a/mail.sdsu.edu/file/d/1TzC1ljUvrejDgtxjkiP6K1EWFnodrv45/view?usp=sharing

[Anonymous]

Sorry, I don't understand.

You've selected 3/15/2019 and all rows with that date are selected. So what is the requirement?

Also, you want to use a CROSSFILTER so I think you should show us the model

[rtaylor]

>

Sorry, I don't understand.

You've selected 3/15/2019 and all rows with that date are selected. So what is the requirement?

I want to be able relate the date to a date table, and plot the data accross time if need be.  Right now the relationship between the date column and the date table are not functioning

>

Also, you want to use a CROSSFILTER so I think you should show us the model

I only want to use crossfilter because it looks like that is only option that will work. If you know of better method, please let me know.

Also I will have to create a sample model with fake data. I will reply once that is complete.

[rtaylor]

Also, you want to use a CROSSFILTER so I think you should show us the model

[rtaylor]

Hello,

Please see below a sample report. Please let me know if you need anything else.

https://drive.google.com/a/mail.sdsu.edu/file/d/1TzC1ljUvrejDgtxjkiP6K1EWFnodrv45/view?usp=sharing

[v-frfei-msft · Accepted solution]

Hi @rtaylor ,

Please update the relationship between tables as below.

After that, set the interactions of your visuals to filter. Then we can get the excepted result.

Pbix as attached.