Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ElliotP
Post Prodigy
Post Prodigy

Easy Measure

Afternoon,

 

I've got some data which comes in quarterly and I'd like to show the latest value and then a measure to show the value before that assuming it isn't null.

 

I have my date columns, my two data columns (just numbers). I created an index.

 

Any idea on how to create a measure that would show the latest value if it isn't blank and then a measure to do the same but -1 from the index.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

I imagine that there are easier ways to do this, but one way would be to create a calculated table:

 

TableData1 = CALCULATETABLE(Data,Data[Data1]<>BLANK())

Then you measure:

 

Measure Data 1 = CALCULATE(SUM(TableData1[Data1]),FILTER(TableData1,TableData1[Index]=MAX(TableData1[Index])))

The first part ensures no blanks, the second part filters to the max of the index.

 

My sample data was this:

 

Index         Date          Data1   Data2

13/1/20171020
23/2/20172030
33/3/20173040
43/4/201740 
53/5/2017 60

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

I imagine that there are easier ways to do this, but one way would be to create a calculated table:

 

TableData1 = CALCULATETABLE(Data,Data[Data1]<>BLANK())

Then you measure:

 

Measure Data 1 = CALCULATE(SUM(TableData1[Data1]),FILTER(TableData1,TableData1[Index]=MAX(TableData1[Index])))

The first part ensures no blanks, the second part filters to the max of the index.

 

My sample data was this:

 

Index         Date          Data1   Data2

13/1/20171020
23/2/20172030
33/3/20173040
43/4/201740 
53/5/2017 60

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

First part works a charm thank you.

 

How would I create a measure which was one less from the index (to show the prior value);

 

I've tried either version of this to no help;

 

Measure Data Income-1 = CALCULATE(SUM(TableData1[Income%Change]),FILTER(TableData1,TableData1[Index]=MAX(TableData1[Index]) - 1))

Well, you could always do something like:

 

TableData2 = FILTER(TableData1,Tabledata1[Index]<MAX(TableData1[Index]))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Would do you think the measure would be for the value preceding the latest value? = max -1 type situation?

turns out that's how to do it lads. thank you so much for your help.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.