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 everyone, hoping for you and your family safety nowadays,
Is it possible to create a measure that I will filter the date of one column based on the last date available from another column? The purpose of this formula is to compare two values from the same day. Below is my formula Sample;
Solved! Go to Solution.
Hi @Anonymous
Sorry again for the late reply!
Pls see the solution below:
1. Create a measure as below:
Measure =
var _maxdate=MAXX(ALL('Original Date Same Year Same Day Year ValueTable'),'Original Date Same Year Same Day Year ValueTable'[Original Date])
Return
CALCULATE(SUM('Original Date Same Year Same Day Year ValueTable'[Value]),FILTER('Original Date Same Year Same Day Year ValueTable','Original Date Same Year Same Day Year ValueTable'[Year]=2019&&'Original Date Same Year Same Day Year ValueTable'[Same Year]<=_maxdate))
And you will see:
For the related .pbix file,pls click here.
Hi @Anonymous ,
Sorry for the late reply.Are the 2 columns in the same table?If not,does the 2 tables have relationships betwee?Can you provide more details such as sample data and the expected output to make your require more clear?
Hello @v-kelly-msft ,
Yes, two columns are in the same table.
I have a table with 4 date columns and 1 value as shown below;
Original Date | Same Year | Same Day | Year | Value |
January 1, 2020 | January 1, 2020 | January 1, 2020 | 2020 | 231 |
January 2, 2020 | January 2, 2020 | January 2, 2020 | 2020 | 214 |
January 3, 2020 | January 3, 2020 | January 3, 2020 | 2020 | 321 |
January 4, 2020 | January 4, 2020 | January 4, 2020 | 2020 | 531 |
January 5, 2020 | January 5, 2020 | January 5, 2020 | 2020 | 613 |
January 1, 2019 | January 1, 2020 | December 31, 2019 | 2019 | 61 |
January 2, 2019 | January 2, 2020 | January 1, 2020 | 2019 | 35 |
January 3, 2019 | January 3, 2020 | January 2, 2020 | 2019 | 213 |
January 4, 2019 | January 4, 2020 | January 3, 2020 | 2019 | 534 |
January 5, 2019 | January 5, 2020 | January 4, 2020 | 2019 | 742 |
January 6, 2019 | January 6, 2020 | January 5, 2020 | 2019 | 3 |
January 7, 2019 | January 7, 2020 | January 6, 2020 | 2019 | 6,342 |
I want to Sum up the value column by YTD, MTD, or Last date by the same day or same year column which will always be based on the last date of the original column. I tried some measure but it is not working;
Hi @Anonymous
Sorry again for the late reply!
Pls see the solution below:
1. Create a measure as below:
Measure =
var _maxdate=MAXX(ALL('Original Date Same Year Same Day Year ValueTable'),'Original Date Same Year Same Day Year ValueTable'[Original Date])
Return
CALCULATE(SUM('Original Date Same Year Same Day Year ValueTable'[Value]),FILTER('Original Date Same Year Same Day Year ValueTable','Original Date Same Year Same Day Year ValueTable'[Year]=2019&&'Original Date Same Year Same Day Year ValueTable'[Same Year]<=_maxdate))
And you will see:
For the related .pbix file,pls click here.
Hi @Anonymous
Sorry again for the late reply!
Pls see the solution below:
1. Create a measure as below:
Measure =
var _maxdate=MAXX(ALL('Original Date Same Year Same Day Year ValueTable'),'Original Date Same Year Same Day Year ValueTable'[Original Date])
Return
CALCULATE(SUM('Original Date Same Year Same Day Year ValueTable'[Value]),FILTER('Original Date Same Year Same Day Year ValueTable','Original Date Same Year Same Day Year ValueTable'[Year]=2019&&'Original Date Same Year Same Day Year ValueTable'[Same Year]<=_maxdate))
And you will see:
For the related .pbix file,pls click here.
Up
Help please.
Up
Help!
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |