Forum Discussion

marcus505's avatar
marcus505
Frequent Visitor
8 years ago
Solved

Fiscal Year Calculated Columns

Hi,   I am trying to create calculated columns that bring out results per Quarter / month per month based on type of caller.     For example   If Month = April and Year = 2018 and Type ...
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    Hi marcus505,

     

    Yes, here you should update your formula like this.

     

    Measure = CALCULATE(SUM(fabric_swishs[fabric_typeofcaller]),FILTER(fabric_swishs,fabric_swishs[fabric_dateofcall].[Month]="August" && fabric_swishs[fabric_dateofcall].[Year]=2018 && fabric_swishs[fabric_typeofcaller]=

555510002))

    Regards,

    Frank

v-frfei-msft's avatar
v-frfei-msft
Community Support
8 years ago

Hi marcus505,

 

Based on my test, we can use the formula to get the result as we excepted.

 

Measure = CALCULATE(SUM(fabric_swishs[values]),FILTER(fabric_swishs,fabric_swishs[month]="April" && fabric_swishs[Year]=2018 && fabric_swishs[Type of caller]="email"))

 

For more details, please check the pbix as attached. If it doesn't meet your requirement, kindly share your sample data to me.

 

https://www.dropbox.com/s/socoo6aggzse5e7/Fiscal%20Year%20Calculated%20Columns.pbix?dl=0

 

Regards,

Frank

marcus505's avatar
marcus505
Frequent Visitor
8 years ago

Thanks, I used the following but results in an error :

 

SyntaxEditor Code Snippet

Measure = CALCULATE(SUM(fabric_swishs[fabric_typeofcaller]),FILTER(fabric_swishs,fabric_swishs[fabric_dateofcall].[Month]="August" & fabric_swishs[fabric_dateofcall].[Year]=2018 && fabric_swishs[fabric_typeofcaller]="Family"))

Table name

 

fabric_swishs

Pick list that has multiple values

fabric_typeofcaller

 

Month I am filtering on

 

fabric_dateofcall

 

Error

 

 

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    8 years ago

    Hi marcus505,

     

    Could you please share your pbix to me? You can upload your pbix to dorpbox and paste the link here directly.

     

    Regards,

    Frank

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        8 years ago

        Hi marcus505,

         

        The highlighted part in your formula, there is no "Family" in the column. So the error will occur. If we delete the part and update the formula to a new one, that will work well.

         

        Measure = CALCULATE(SUM(fabric_swishs[fabric_typeofcaller]),FILTER(fabric_swishs,fabric_swishs[fabric_dateofcall].[Month]="August" && fabric_swishs[fabric_dateofcall].[Year]=2018 && fabric_swishs[fabric_typeofcaller]="Family"))
        Measure2 = CALCULATE(SUM(fabric_swishs[fabric_typeofcaller]),FILTER(fabric_swishs,fabric_swishs[fabric_dateofcall].[Month]="August" && fabric_swishs[fabric_dateofcall].[Year]=2018))

         

        Please check the pbix as attached.

         

        https://www.dropbox.com/s/hbu25f4vct5lbky/FiscalData.pbix?dl=0

         

        Regards,

        Frank