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
Anonymous
Not applicable

Calculate a mesure based on the end date

Hi, 

 

I have a date rang slicer. i would like to show mesure calculations based on the end date of the slicer. Thats means wanted to eliminate the start date . 

 

Lets say date range slicer is selected dates as 1/1/2019 and 3/1/2019. based on this slicer selections i would like to show mesure always calculate based onthe  end date. ignore the start date. 

 

Any suggestion on this. 

6 REPLIES 6
v-evelk
Employee
Employee

Hi,

 

If I correctly understand, you want to show only the latest value in the selected range?

 

If so, you can try different card visuals. I received such behavior usign of Card with States by OKViz for instance.

 

 

LatestValue.PNG

 

Kind Regards,

 

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

Anonymous
Not applicable

Let’s say for an example  .  In the fast table I have loaded 3 years of data( ex: year 2015,2016 and 2017) . In the power bi data modeling let’s say we have a  fact table and one time dimensional table .

 

One Date rage slicer has been added on the Power bi Dashboard along with one KPI as shown in below.

 

As per the example user has selected certain date rang selections ( ex: 4/22/2017 to 7/5/2017) , here I would expecting to display the KPI values as of 7/5/2017. That’s means since 2015 to as of 7/5/2017.

 

pwi.jpg

I am not sure that such visual exists but it is definitely possible to implement.

 

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

Btw, you can try the following option of Timeline Slicer visual

"Force selection" --> "Latest available period".

Maybe it will help to solve your question.

 

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

Anonymous
Not applicable

For this , I created a DAX function as below . its function correctly the way I want.. Means if  I select march 29 on my date slicer as an end date and the below measure is calculating based on my selected end date not on my selected start date..

 

In this example.. The fact table doesn’t have data from march 1 st to march 13, for an Empty date the below DAX function is considering as number 4 .. am not sure on what bases its considering as a values 4 for empty dates..

 

Any Idea?

 

Count of sales = CALCULATE(DISTINCTCOUNT(Sales[order]),sales[orders]<>BLANK(),

FILTER(ALL ( 'DATE_DIM_V'[CALENDAR_DATE] ), 'DATE_DIM_V'[CALENDAR_DATE] <= MAX ( 'DATE_DIM_V'[CALENDAR_DATE] )  ))

 

test.jpg

DAX is out of our expertise because it is Desktop Client feature.

It is better to ask about DAX queries in the Desktop thread.

 

Kind Regards,

 

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

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.