- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Issue using Date measure to filter a summing measure
I have created a Start Date measure to calculate the latest start date:
I am trying to use this date measure to filter data for a summing measure:
This measure is returning the Sum of the amount from all years.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi ,@nkillia
According to you description, you can try to add “all” function in you your measures “Max Current Date” .
I don't have the formula of your measure “Max Current Date” .
So here is a simple sample using some other data:
Max Current Date = CALCULATE(MAX(financials[Date].[Date]),all(financials))
Max Current Date without all = MAX(financials[Date])
By comparison ,you will find the measure “Max Current Date” ignore any filters that might have been applied .
If it doesn't meet your requirement, kindly share your sample data and expected result if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @v-easonf-msft ,
Here is an example of what I am hoping to acomplish:
My MAX Date Formula is =Max('table'[Date])
My Start Date Formula is = Date(YEAR([Max Date]),1,1)
I am trying to sum only the data that has a date greater than 1/1/2019
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @nkillia
Try this,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Anonymous ,
Thank you, is there a way to use the start date measure instead of YEAR(MAX(Customer[Monthly])) part?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @nkillia
Modify your measure
start date measure=YEAR(MAX(Customer[Monthly]))
and use this measure in your filter part of formula .
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This may be a better example of what I am trying to accomplish.
1. Return the max date of type A
Max A Date =CALCULATE(MAX('Table'[Date]),'Table'[Type] <> "B")
2. Return start of the year for that date.
A Start Year = DATE(Year([Max A Date]),1,1)
3. Return sum of the amount between those two dates excluding type B
The test data can be found below:
Date Amount Type
1/1/2018 | 1 | A |
2/1/2018 | 1 | A |
3/1/2018 | 1 | A |
4/1/2018 | 1 | A |
5/1/2018 | 1 | A |
6/1/2018 | 1 | A |
7/1/2018 | 1 | A |
8/1/2018 | 1 | A |
9/1/2018 | 1 | A |
10/1/2018 | 1 | A |
11/1/2018 | 1 | A |
12/1/2018 | 1 | A |
1/1/2019 | 1 | A |
2/1/2019 | 1 | A |
3/1/2019 | 1 | A |
4/1/2019 | 1 | A |
5/1/2019 | 1 | A |
6/1/2019 | 1 | A |
7/1/2019 | 1 | A |
8/1/2019 | 1 | A |
9/1/2019 | 1 | A |
10/1/2019 | 1 | A |
1/1/2019 | 1 | B |
2/1/2019 | 1 | B |
3/1/2019 | 1 | B |
4/1/2019 | 1 | B |
5/1/2019 | 1 | B |
6/1/2019 | 1 | B |
7/1/2019 | 1 | B |
8/1/2019 | 1 | B |
9/1/2019 | 1 | B |
10/1/2019 | 1 | B |
11/1/2019 | 1 | B |
12/1/2019 | 1 | B |
1/1/2020 | 1 | B |
2/1/2020 | 1 | B |
3/1/2020 | 1 | B |
4/1/2020 | 1 | B |
5/1/2020 | 1 | B |
6/1/2020 | 1 | B |
7/1/2020 | 1 | B |
8/1/2020 | 1 | B |
9/1/2020 | 1 | B |
10/1/2020 | 1 | B |
11/1/2020 | 1 | B |
12/1/2020 | 1 | B |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Anonymous Thank you, I believe that worked for me.
Thank you again for all your time and effort. I truly appreciate it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @az38,
I tried the following and received this error: "Column 'Start Date' cannot be found or may not be used in this expression"
Is there any expression that would be able to grab the value of the measure?
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
07-22-2024 11:52 AM | |||
02-09-2024 07:08 AM | |||
Anonymous
| 11-29-2023 06:04 AM | ||
06-18-2024 07:36 AM | |||
01-12-2024 07:38 AM |
User | Count |
---|---|
121 | |
80 | |
47 | |
45 | |
32 |
User | Count |
---|---|
174 | |
90 | |
69 | |
47 | |
46 |