cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Identify latest result and count

What DAX, or combination of DAX, do I need to use to achieve the following please:

I have a data source (excel based rn) which lists a project reference number, the date and the RAG status of that project. This data source is going to grow as each new months status updates are gathered together and added to the list.

What I would like to do is have a measure for each RAG outcome which tots up the latest update - whether that's all the most recent months entries or from months ago (it could be that there are no updates for a few months on the trot).

Data is organised in columns with reference numbers, date of update and RAG outcome (red, amber or green).  In a matrix this looks like this:

And you can see how some references have an updated RAG for every month, some have gaps and some have none.

How do I count the most recent RAG statuses only (regardless of whether june or april for example), one time for each reference number only and for each RAG (3 sep measures)..??

I've tried other solutions to similar queries but relicating them hasn't worked and only given me the single most recent result.

1 ACCEPTED SOLUTION
Solution Sage

Hi:

I'll copy below date table DAX. In Modeling go to New Table> add in Date Table Code. Mark as Date table. Create realtionship with your fact table (table with data in it). Now you can use time intel calculations.

Last RAG Value = LASTNONBLANKVALUE(Dates[Date], [RAG Measure])

If this doesn't work, please share example data. Thanks and good luck!

Date Table Code:

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

VAR FiscalMonthEnd = 12

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

-- Add the calendar month and other month related data for each date

Years,

"Month",MONTH([Date]),

"Year and Month Number",FORMAT([Date],"YYYY-MM"),

"Year and Month Name",FORMAT([Date],"YYYY-MMM"),

"Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

"Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date

Months,

"Quarter",ROUNDUP(MONTH([Date])/3,0),

"Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date

Quarters,

"Day",DAY([Date]),

"Day Name",FORMAT([Date],"DDDD"),

"Day Of Week",WEEKDAY([Date]),

"Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date

Days,

"Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

"Week of Year (Sunday)",WEEKNUM([Date],1),

"Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

Weeks,

"Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays

2 REPLIES 2
Solution Sage

Hi:

I'll copy below date table DAX. In Modeling go to New Table> add in Date Table Code. Mark as Date table. Create realtionship with your fact table (table with data in it). Now you can use time intel calculations.

Last RAG Value = LASTNONBLANKVALUE(Dates[Date], [RAG Measure])

If this doesn't work, please share example data. Thanks and good luck!

Date Table Code:

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

VAR FiscalMonthEnd = 12

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

-- Add the calendar month and other month related data for each date

Years,

"Month",MONTH([Date]),

"Year and Month Number",FORMAT([Date],"YYYY-MM"),

"Year and Month Name",FORMAT([Date],"YYYY-MMM"),

"Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

"Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date

Months,

"Quarter",ROUNDUP(MONTH([Date])/3,0),

"Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date

Quarters,

"Day",DAY([Date]),

"Day Name",FORMAT([Date],"DDDD"),

"Day Of Week",WEEKDAY([Date]),

"Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date

Days,

"Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

"Week of Year (Sunday)",WEEKNUM([Date],1),

"Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

Weeks,

"Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays

Regular Visitor

Thank you so much! I already had a date table, used that and it worked. Thank you!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors