Need help with DAX formula for using date column and location column

[KDiSanto]

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:

[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.

[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 

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 · Accepted solution]

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!

[tamerj1]

Hi @KDiSanto 

are you summarizing by count or distinct count?

[KDiSanto]

No I am not summarizing by count and I even went in to the exercise location column and transformed data to add a measurement and seems to not like it.

[tamerj1]

@KDiSanto 

What is the dax code of the measure?

[KDiSanto]

LastFullScaleIn3Years = IF (YEAR ( TODAY () ) - YEAR ( 'OPS FCC Exercise Events List'[End Date] ) <= 3&& 'OPS FCC Exercise Events List'[ExerciseType] = "Full Scale"  , "Yes","No") 

I tried creating a measure DAX code but didn't know where to begin on this one.

[tamerj1]

@KDiSanto 

That is fine. What you have just provided is the calculated column that is being used as a filter or legend in the pie chart visual. My question is what are you placing in the Values of the pie chart visual?

[KDiSanto]

The values are the full scale exercises completed by each site for the last 3 years; however, if the site has one exercise each year I want that to only return 1 as the value for that site because they are in compliance for completing a full scale within the 3 year period. 

[Greg_Deckler]

@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.

[KDiSanto]

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