Forum Discussion
Need help with DAX formula for using date column and location column
I have 48 locations; however, when I try to apply a last date/most recent date/year it doesn't total the 48 it is pulling all records i.e. Albany and if there were exercises conducted in 2021-2025 it doesnt apply a one count, or an inventory date, etc. It will show for example:
Hi KDiSanto
Thank you for reaching out to the Microsoft Fabric Community Forum. Also tamerj1 and Greg_Deckler for your quick response on this topic.
Regarding your query on creating a DAX formula to use date column and location column
Please consider the below DAX
LocationsWithFSEIn3Years = CALCULATE( DISTINCTCOUNT('OPS FCC Exercise Events List'[Location Name]), FILTER( 'OPS FCC Exercise Events List', 'OPS FCC Exercise Events List'[ExerciseType] = "Full Scale" && DATEDIFF('OPS FCC Exercise Events List'[End Date], TODAY(), YEAR) <= 3 ) )
If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.
Thank You!
12 Replies
- Greg_Deckler
Community Champion
KDiSanto Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Below is some sample data, I was able to create the column for the LastFullScaleIn3Years and apply this formula to return a yes/no.
LastFullScaleIn3Years = IF (YEAR ( TODAY () ) - YEAR ( 'Exercise Events List'[End Date] ) <= 3&& 'Exercise Events List'[ExerciseType] = "Full Scale" , "Yes","No")However, when I apply the LastFullScaleIn3Years filter for No it counts 87 and Yes counts 28; however, I need the numbers to total 48, since I have 48 locations and I reviewed the table and the only duplicate I found was San Juan so I know it is not a duplication error.I am also using this table to establish a pie chart visual that shows the plan date and inventory date broken down by fiscal year; however, this as well is not totalling the 48 or less that 48 per fiscal year i.e FY23 total is 592, etc. which leads me to believe that is totalling all records based on having more than one submission date. This is only for the the plan and inventory date.
Location Name Submission Date Exercise Type End Date Fiscal Year Last FSE within 3 years Plan Date Inventory Date San Juan 3/13/2025 Full Scale 5/13/2025 FY25 Yes 2/28/2025 5/13/2025 San Juan 4/10/2025 Full Scale 5/15/2025 FY25 Yes Little Rock Full Scale 4/4/2024 FY 24 Yes 10/4/2024 7/31/2024 Tampa Full Scale 4/13/2023 FY23 Yes 02/08/2023 3/8/2023 Denver Full Scale 3/1/2022 FY22 Yes 10/04/2022 10/12/2022
v-karpurapudCommunity Support
Hi KDiSanto
Thank you for reaching out to the Microsoft Fabric Community Forum. Also tamerj1 and Greg_Deckler for your quick response on this topic.
Regarding your query on creating a DAX formula to use date column and location column
Please consider the below DAX
LocationsWithFSEIn3Years = CALCULATE( DISTINCTCOUNT('OPS FCC Exercise Events List'[Location Name]), FILTER( 'OPS FCC Exercise Events List', 'OPS FCC Exercise Events List'[ExerciseType] = "Full Scale" && DATEDIFF('OPS FCC Exercise Events List'[End Date], TODAY(), YEAR) <= 3 ) )
If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.
Thank You!
- v-karpurapud
Hi KDiSanto
We have not received a response from you regarding the query and were following up to check if you have found a resolution from the information provided below. If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.
Thank You! - v-karpurapud
Hi KDiSanto
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you. - v-karpurapud
Hi KDiSanto
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
