cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sumanchintala Regular Visitor
Regular Visitor

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 Senior Member
Senior Member

Re: Calculate a mesure based on the end date

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

Sumanchintala Regular Visitor
Regular Visitor

Re: Calculate a mesure based on the end date

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

v-evelk Senior Member
Senior Member

Re: Calculate a mesure based on the end date

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

v-evelk Senior Member
Senior Member

Re: Calculate a mesure based on the end date

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

Sumanchintala Regular Visitor
Regular Visitor

Re: Calculate a mesure based on the end date

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

v-evelk Senior Member
Senior Member

Re: Calculate a mesure based on the end date

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 426 members 4,151 guests
Please welcome our newest community members: