Forum Discussion

ronenbitman's avatar
ronenbitman
Frequent Visitor
9 years ago

Slicer & CalcilatedColumn

Hi, i have a report with a Slicer "As of Date" the is linked to the DateDimension, all works great, i need to add a Histogram for Age of the Customer Facts, as such i added a calculated co...
Baskar's avatar
Baskar
Resident Rockstar
9 years ago

For my understanding do u want get the age based on slicer selction date ?

 

If yes your aproach is wrong . the reason is if u create calculated column it is static it won't change while slicer change.

 

u have to create calculated Measure instead of Calculated Column.

 

Share ur column and sample data i will help u.

 

The idea is create new measure :

Get DOB in Var , like Var DOB = Max(DateOfBirth)

Get Max Date on Slicer   like Var Max_Date = Max('Date Dimension'[Date]).

 

then final Datediff : Return Datediff(DOB,Max_Date,DAY).

 

Note: 

If ur DOB is greater then slicer it will give u the error.

 

So add one condition in final return : 

 

Return if ( Max_Date < DOB, Blank(), Datediff(DOB,Max_Date,DAY))

 

Let me know , any help

  • ronenbitman's avatar
    ronenbitman
    Frequent Visitor
    9 years ago

    Hi Baskar,

     

    thanks for the answer

     

    my initial question was not the actual one as i tried to make the explanation of my schema easier, but i understand the idea behind what you are saying, however i still unable to see how to accomplish this,
    i am not able to attach the data but i attached the Schema an (hopefully) added the information that is evolved in my requirement.

    basicly i have two requirements

    - show the count of not accredited Investor, so i added the measure

    Not Accredited Investor Count = COUNTX(Filter('Investor Facts','Investor Facts'[Accredited Month Age]<>0),'Investor Facts'[Accredited Month Age] )

    Facts'[Accredited Month Age] )]

    - show a histogram of Accredited Month Age - so i added a calculated column

     

    Accredited Month Age = 
	VAR InternalSelectedDate = 'Date Dimension'[SelectedDate]
	VAR In12Month = EOMONTH([Latest Subscription Activity],12)
    VAR EndOfAccreditedPeriod = 
        IF(
            DAY([Latest Subscription Activity])>DAY(In12Month),
                    DATE(year(In12Month),month(In12Month),day(In12Month)),
                    DATE(year(In12Month),month(In12Month),day([Latest Subscription Activity]))
        )
    RETURN
        if(
            'Investor Facts'[Investor Accreditied Class]="A" || 'Investor Facts'[Investor Accreditied Class]="B" || 'Investor Facts'[Investment Type]="Provident Fund"
            , 0
            , if(EndOfAccreditedPeriod > NOW(), DATEDIFF(InternalSelectedDate,EndOfAccreditedPeriod,MONTH), 0)
        )

     

    i added a workaround as the DateDiff is unable to add dates that are not defined in the 'Date Dimension' Table

     

    could you please try to point me to the right way to implement this

     

     

     

     

    many thanks for the help

     

     

     

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      Hi ronenbitman,

      >>i added a workaround as the DateDiff is unable to add dates that are not defined in the 'Date Dimension' Table

       

      Based on my understanding, using the DateDiff function in "Accredited Month Age" measure returns an error or something else? Could you please post 'Investor Facts' table for further analysis? If your data is private, you can create sample data in similar format.

       

      Best Regards,
      Angelia

      • ronenbitman's avatar
        ronenbitman
        Frequent Visitor
        9 years ago

        Hi,

         

        no there is no error when i use the DateDiff, it just return empty value which eventually prevents me from receiving the requested outcome.
         attached a PQ to create the table

        Table.FromRows({
{1,FALSE,,"A","Private",,,,,,"",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Private",253.1223275,"Private",31/07/2016,8},
{2,FALSE,,"C","Private",,,,,,"Israel",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,22/11/2016 11:28,22/11/2016 11:28,,,,,"Active",,,"Equity",,"","",,"Other","Private",52.01478875,"Private",31/10/2016,11},
{3,FALSE,,"D","Company",,,,,,"Guernsey",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,11/12/2016 16:45,11/12/2016 16:45,,,,,"Active",,,"Equity",,"","",,"Other","Company",4068.535781,"Company",31/01/2016,2},
{4,FALSE,,"E","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Private",161.4756306,"Private",31/05/2016,6},
{5,FALSE,,"F","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Family Office",260.844415,"Family Office",31/03/2016,4},
{6,FALSE,,"G","Company",,,,,,"Israel",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,11/12/2016 16:58,11/12/2016 16:58,,,,,"Active",,,"Equity",,"","",,"Other","Investment House",262.6206747,"",31/08/2016,9},
{7,FALSE,,"H","Private",,,,,,"",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:05,01/11/2016 12:05,,,,,"Active",,,"Equity",,"","",,"Other","Private",97.16964962,"Private",31/08/2016,9},
{8,FALSE,,"I","Company",,,,,,"Switzerland",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:05,01/11/2016 12:05,,,,,"Active",,,"Equity",,"","",,"Other","Company",506.244655,"Company",31/07/2016,8},
{9,FALSE,,"J","Private",,,,,,"Israel",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,22/11/2016 11:28,22/11/2016 11:28,,,,,"Active",,,"Equity",,"","",,"Other","Private",260.0739437,"Private",31/10/2016,11},
{10,FALSE,,"K","Private",,,,,,"",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,30/11/2016 13:43,30/11/2016 13:43,,,,,"Active",,,"Equity",,"","",,"Other","Family Office",525.2413493,"Family Office",31/08/2016,9},
{11,FALSE,,"L","Company",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",02/01/2017 02:36,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Company",3155.20719,"Company",31/01/2016,2},
{12,FALSE,,"DS","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"AG",27/12/2016 07:13,,01/11/2016 12:05,01/11/2016 12:05,,,,,"Active",,,"Equity",,"","",,"Other","Private",156.1277687,"Private",31/03/2013,0},
{13,FALSE,,"M","Private",,,,,,"UK",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Private",1093.030333,"Private",29/02/2016,3},
{14,FALSE,,"FF","Company",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"AG",02/01/2017 02:36,,01/11/2016 12:05,01/11/2016 12:05,,,,,"Active",,,"Equity",,"","",,"Other","Company",2394.249123,"Company",31/03/2013,0},
{15,FALSE,,"N","Private",,,,,,"",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,28/11/2016 09:15,28/11/2016 09:15,,,,,"Active",,,"Equity",,"","",,"Other","Private",782.6398577,"Private",30/04/2016,5},
{16,FALSE,,"AA","Company",,,,,,"Israel",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,11/12/2016 16:44,11/12/2016 16:44,,,,,"Active",,,"Equity",,"","",,"Other","Company",5426.93198,"Company",31/07/2016,8},
{17,FALSE,,"AB","Company",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Company",806.5134905,"Company",30/04/2016,5},
{18,FALSE,,"AC","Company",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",04/01/2017 16:09,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Company",5468.522038,"Company",31/08/2016,9},
{19,FALSE,,"AD","Private",,,,,,"Israel",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,22/11/2016 11:27,22/11/2016 11:27,,,,,"Active",,,"Equity",,"","",,"Other","Private",260.0739437,"Private",31/10/2016,11},
{20,FALSE,,"AE","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,20/12/2016 07:56,20/12/2016 07:56,,,,,"Active",,,"Equity",,"","",,"Other","Private",717.2395009,"Private",31/01/2016,2},
{21,FALSE,,"AF","Private",,,,,,"Israel",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Private",525.2413493,"Private",31/08/2016,9},
{22,FALSE,,"AG","Private/ Family office",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Family Office",518.01409,"Family Office",30/04/2016,5},
{23,FALSE,,"AH","Company",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,11/12/2016 16:37,11/12/2016 16:37,,,,,"Active",,,"Equity",,"","",,"Other","Family Office",525.2413493,"Family Office",31/08/2016,9},
{24,FALSE,,"AI","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",01/11/2016 12:04,"g",23/11/2016 16:52,,22/11/2016 13:13,22/11/2016 13:13,,,,,"Active",,,"Equity",,"","",,"Other","Private",0,"Private",31/05/2016,6},
{25,FALSE,,"BB","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,11/12/2016 16:50,11/12/2016 16:50,,,,,"Active",,,"Equity",,"","",,"Other","Family Office",518.0244503,"Family Office",30/04/2016,5},
{26,FALSE,,"BC","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Private",1518.733965,"Private",31/07/2016,8},
{27,FALSE,,"BD","Company",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Company",207.205636,"Company",30/04/2016,5},
{28,FALSE,,"DD","Private",,,,,,"",,,,,,,,,,,,,,,"AQ",24/11/2016 07:43,"A",20/12/2016 07:55,"g",21/12/2016 16:10,,20/12/2016 08:00,20/12/2016 08:00,,,,,"Active",,,"Equity",,"yes","C",,"Israel","Private",260.3472779,"Private",30/11/2016,12},
{29,FALSE,,"EE","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Israel","Family Office",260.844415,"Family Office",31/03/2016,4}
})

        kind regards,

        Ronen