Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Any help would be greatly appreciated
Solved! Go to 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.
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]))
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
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
>
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |