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 EntryDate Month Amount1/15/20197/1/2019 31,564.001/15/20198/1/2019 646,512.001/15/20199/1/2019 1,261,460.001/15/201910/1/2019 1,876,408.001/15/201911/1/2019 2,491,356.001/15/201912/1/2019 3,106,304.001/15/20191/1/2020 3,721,252.001/15/20192/1/2020 4,336,200.001/15/20193/1/2020 4,951,148.001/15/20194/1/2020 5,566,096.001/15/20195/1/2020 6,181,044.001/15/20196/1/2020 6,795,992.003/15/20197/1/2019 30,010.003/15/20198/1/2019 644,958.003/15/20199/1/2019 1,259,906.003/15/201910/1/2019 1,874,854.003/15/201911/1/2019 2,489,802.003/15/201912/1/2019 3,104,750.003/15/20191/1/2020 3,719,698.003/15/20192/1/2020 4,334,646.003/15/20193/1/2020 4,949,594.003/15/20194/1/2020 5,564,542.003/15/20195/1/2020 6,179,490.003/15/20196/1/2020 6,794,438.004/15/20197/1/2019 84,664.004/15/20198/1/2019 699,612.004/15/20199/1/2019 1,314,560.004/15/201910/1/2019 1,929,508.004/15/201911/1/2019 2,544,456.004/15/201912/1/2019 3,159,404.004/15/20191/1/2020 3,774,352.004/15/20192/1/2020 4,389,300.004/15/20193/1/2020 5,004,248.004/15/20194/1/2020 5,619,196.004/15/20195/1/2020 6,234,144.004/15/20196/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 EntryDate Month: Date Relationship Projection3/15/20197/1/2019 30,010.003/15/20198/1/2019 644,958.003/15/20199/1/2019 1,259,906.003/15/201910/1/2019 1,874,854.003/15/201911/1/2019 2,489,802.003/15/201912/1/2019 3,104,750.003/15/20191/1/2020 3,719,698.003/15/20192/1/2020 4,334,646.003/15/20193/1/2020 4,949,594.003/15/20194/1/2020 5,564,542.003/15/20195/1/2020 6,179,490.003/15/20196/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

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.

rtaylor

Helper III

6 years ago

Solved

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

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

Need Help

v-frfei-msft
6 years ago
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.

SampleforProjection.pbix435 KB

6 Replies

Anonymous
Not applicable
6 years ago
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
  Helper III
  6 years ago
  >
  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
    Helper III
    6 years ago
    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
Community Support
6 years ago
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
  Helper III
  6 years ago
  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

Forum Discussion

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

6 Replies

Related content

Relationship

Relationships

Relationship Issue

Creating relationships

PowerBI Relationships