cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
laurenN
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:

laurenN_0-1652968066710.png

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
Whitewater100
Super User
Super User

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

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

 

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

VAR Months = ADDCOLUMNS(

    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   

VAR Quarters = ADDCOLUMNS(

    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   

VAR Days = ADDCOLUMNS(

    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   

VAR Weeks = ADDCOLUMNS(

    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.

var WorkingDays = ADDCOLUMNS(

    Weeks,

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

 

RETURN WorkingDays

View solution in original post

2 REPLIES 2
Whitewater100
Super User
Super User

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

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

 

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

VAR Months = ADDCOLUMNS(

    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   

VAR Quarters = ADDCOLUMNS(

    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   

VAR Days = ADDCOLUMNS(

    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   

VAR Weeks = ADDCOLUMNS(

    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.

var WorkingDays = ADDCOLUMNS(

    Weeks,

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

 

RETURN WorkingDays

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors