Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Getting date difference based on selected date

Hi,   I have a dataset that contains Order Date,Region,Account Number. Order Date has Data from 1/1/2014 to 31/12/2018. user want to pass a date value may be through slicer/parameter possibly. ex...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

hi, Anonymous 

You could get it as below:

Step1:

Create another date table for Date slicer/parameter.

eg.  new table

Date = CALENDARAUTO()
Step2:
selected date= SELECTEDVALUE( Date[Date] )
Step3:
No of days =
DATEDIFF ( CALCULATE ( MAX ( Table[Order Date] ) ), [Selected Date], DAY )
 
Then just drag Order date filed and these two measure into a table visual
 
Best Regards,
Lin
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

     

    Thanks for the calculation.

    I have got the No of days days b/w selected date & order date.

    but the No of days is the measure where I won't be able to get Average of number of days.

    because once I get the No of days I need to calculate average of number of days which is not happening.

    we can get average of No of days if No of days is column instead of measure. but I try to create No of days as column it's giving me blank data.

     

    Is there anyway that we can get Average of No of Days per Region(or any dimension).

    Note: It's an important requiremnet for me to get Average of Numbe of days, almost 15 dashbaord using the same metric.

     

    A quick help would be much appreciated.

     

    Regards

    Harsha

    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      6 years ago

      hi, Anonymous 

      This looks like a measure totals problem. Very common. See this post about it
      https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

      Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
      https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

       

      For your case, you just need to change SUMX to AVERAGEX,  you could try this formula:

      Measure = 
VAR _table =
    ADDCOLUMNS (
        'Table',
        "_selected date", [selected date],
        "_no of days", [No of days]
    )
RETURN
    AVERAGEX ( FILTER ( _table, [Order Date] <= [_selected date] ), [_no of days] )

      Result:

      and here is my sample pbix file, please try it.

       

      Best Regards,

      Lin

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi,

         

        Thanks for the prompt response. your calculation helps me to achieve my requirement for some level.

        what I am looking at is let me expalin clearly below, I hope you can provide solution for my problem after you go through the below requirement,

         

        Final claculation that I am expecting such as below,

         

         Age of Open Items = CALCULATE(AVERAGE('Open Items'[No. of days Open]),FILTER('Open Items','Open Items'[No. of days Open]>30 && (('Open Items'[FN Account]>="200000" && 'Open Items'[FN Account]<="299999")) && not('Open Items '[FN Account]) in {"20100","21123","21435"} && (ISBLANK('Open Items'[Posting Date]) || 'Open Items'[Posting Date]>[DateSelected]) && 'KPI Open Items Prepayment-Trade&Sundry'[Account Type]<>"BA")).   - Final calculation

         

        from the above,
        No. of days open = [DateSelected] - Entry Date.

         

        I managed to acheive my above requiremnt to get Average no of days on specific filtering conditions.
        for this I have created Period Close Parameter and pass the Entry date values while creating into Period Close.
        Later I created below calculation in my base table (Ex: Open Items table)

        DateSelected = SELECTEDVALUE('Period Close'[Period Close]).

         

        but the problem I am facing is everytime to change the value in parameter I should go to edit parameter and change the value from the list of values that I had passed. still it didn't refresh to get the data based on my selection in parameter.


        again I noticed that I published it in services and went to schedule refresh and choose parameters option, there it didn't show me the parameter that I have created and used in desktop(Period Close). it says parameter haven't been defined for this dataset.

         

        Overall I achieved my requirement 80% using parameter properly, but using paramter is very difficult in desktop evrytime I should go to edit parameter and change the value but still it's not giving the selected value what we select in parameter dropdown until we refresh the parameter table manually.

         

        Due to this I was looking at work around with out creating a parameter, may be using a date table how you had mentioned earlier, but here the major problem I am facing is while trying to get Average No.of days open including some filter conditions as No.of days open>60 days && Account filtering & posting date>date selected etc.. In the process I need to make sure my No of days open should be column which helps me to aggreate the data ant any level, in my case it will be average(No of days open) but it's giving me blank as it's a column.
        As you have sent me a calculation of No of days open as measure it gives me No of days open but I can't write average on No of days open measure including all other filterig conditions ref to Age of Open Item calculation that I have provided on Top.

         

        Help I need is that is there a way that parameter will refresh data once we choose from edit parameter by default and the same should be enable in services.
        if not the parameter conecept, using date table how do we achieve Average no of days open including filter conditions as in Age of Open Items as above.

         

        Any help would be much appreciated.