cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sumanchintala Frequent Visitor
Frequent 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
Highlighted
v-evelk Established Member
Established 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 Frequent Visitor
Frequent 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 Established Member
Established 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 Established Member
Established 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 Frequent Visitor
Frequent 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 Established Member
Established 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