cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kprasad
Frequent Visitor

Getting goal as blank and distance as infinity in kpi visual for calculated previous period measure.

Hi,

I am working on a dashboard, in which I am trying to implement a KPI visual for current period vs previous period values. The visual works correct for the end date upto 31st December 2016. But when I change my end date to any date from january 2017 KPI visual starts me showing goal as blank and distance as infinite forselected end date from january 2017.Here I am attaching the

screenshots from the test file.So can any one please help me to find any solution on this..??

Click to download test file
End date is of December 2016End date is of December 2016After selecting End date from january 2017After selecting End date from january 2017

 

 

1 ACCEPTED SOLUTION

@kprasad

 

Please try with following two DAX measures. And drag ‘public facebook_page_data’[Date] into the KPI visual.

 

Cur_Period_Total = 
VAR FirstD =
    MIN ( 'Date select'[Date] )
VAR LastD =
    MAX ( 'Date select'[Date] )
RETURN
    CALCULATE (
        SUM ( 'public facebook_page_data'[Clicks] ),
        FILTER (
            ALL ( 'public facebook_page_data' ),
            'public facebook_page_data'[Date] >= FirstD
                && 'public facebook_page_data'[Date] <= LastD
        )
)
Pre_Period_Total = 
CALCULATE (
    SUM ( 'public facebook_page_data'[Clicks] ),
    ALL ( 'public facebook_page_data' ),
    DATESBETWEEN (
        'Date select'[Date],
        MIN ( 'Date select'[Date] ) - 'Date select'[Selected_Days],
        MIN ( 'Date select'[Date] ) - 1
    )
)

Getting goal as blank and distance as infinity in kpi visual for calculated previous period measure_1.jpg

 

Best Regards,

Herbert

View solution in original post

7 REPLIES 7
tringuyenminh92
Memorable Member
Memorable Member

Hi @kprasad,

 

What is meaning of Previous Period Clicks  and fomula to calcualte it? I think your expression for Previous Period Clicks  is not correct so with 2017, it returns blank and making % infinity

v-haibl-msft
Microsoft
Microsoft

@kprasad

 

If we change end date to 1/1/2017, 1/2/2017…1/5/2017, the KPI visual will not show goal as blank and distance as infinite.

I have a question about the Previous Period Clicks measure, how do you want to calculate it? Is current DAX formula correct?

 

Best Regards,

Herbert

@v-haibl-msft 

I have calculated the Previous Period Clicks measure on the basis of date diffrence from current period context dates.I have used the following formulas to calculate the measure.

1)Start Date = MIN('Date select'[Date])

2)End Date = MAX('Date select'[Date])

3)DateDiff = DATEDIFF([Start Date],[End Date],DAY)+1

4)Previous Period Clicks = CALCULATE(SUM('public facebook_page_data'[Clicks]),DATEADD('Date select'[Date],-[DateDiff],DAY))

 

I have calculted a measure for every formulas mentioned above.The Previous Period Clicks measure returned correct value to me for selectd date range in date filter.I have verified it by using card visual and the values from database.My objective is to get previous period values.

For example:

If I select 7 days date diffrence in date filter;then my calculated previous period measure must return me the values of last 7 days from the start date that I have selectd in date filter.

 

Best Regards,

Prasad

@kprasad

 

In your table of “public facebook_page_data”, the last date is 1/4/2017. The measure of “Previous Period Clicks” will always return the Clicks in previous day of the max selected date.

For example, if we select date range from “3/2/2016” to “1/5/2017”, “Previous Period Clicks” will return the Clicks in 1/4/2017 and it is 1294.

If you select date range from “3/2/2016” to “1/6/2017”, since there is no data for “1/5/2017”, then “Previous Period Clicks” will return blank.

 

Best Regards,

Herbert

@v-haibl-msft,

Thank you for your reply. As per example given by you if I select date range from “3/2/2016” to “1/5/2017” the date difference is 310 days. Then the goal value of previous period must return the sum of clicks between "4/27/2015" and “3/1/2016”. But as the dates before "1/1/2016" are not present in the database so it must consider the Clicks for dates before  "1/1/2016" as zero and must return the sum of clicks from dates "1/1/2016" to  “3/1/2016”. But the value we are  getting in example given by you for previous period clicks is 1294.  which is the on date “1/4/2017”.

  

So,Can you please help me in calculating the formula for previous period.?

 

Best Regards,

Prasad Kulkarni

@kprasad

 

Please try with following two DAX measures. And drag ‘public facebook_page_data’[Date] into the KPI visual.

 

Cur_Period_Total = 
VAR FirstD =
    MIN ( 'Date select'[Date] )
VAR LastD =
    MAX ( 'Date select'[Date] )
RETURN
    CALCULATE (
        SUM ( 'public facebook_page_data'[Clicks] ),
        FILTER (
            ALL ( 'public facebook_page_data' ),
            'public facebook_page_data'[Date] >= FirstD
                && 'public facebook_page_data'[Date] <= LastD
        )
)
Pre_Period_Total = 
CALCULATE (
    SUM ( 'public facebook_page_data'[Clicks] ),
    ALL ( 'public facebook_page_data' ),
    DATESBETWEEN (
        'Date select'[Date],
        MIN ( 'Date select'[Date] ) - 'Date select'[Selected_Days],
        MIN ( 'Date select'[Date] ) - 1
    )
)

Getting goal as blank and distance as infinity in kpi visual for calculated previous period measure_1.jpg

 

Best Regards,

Herbert

Hi @v-haibl-msft,

Thank you for the solution it worked for me.

 

Best Regards,

Prasad Kulkarni

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.