Fiscal Year Calculated Columns

[marcus505]

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 of caller = "email"

then sum values

I am new to PowerBI, this is what i have managed to piece together.

Any help id greatly appreciated.

Thanks,

[v-frfei-msft]

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]

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]

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

[marcus505]

Hi Frank,

Link - https://www.dropbox.com/s/ek3nxiy45et18yq/FiscalData.pbix?dl=0

Thanks a lot !

[v-frfei-msft]

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

[marcus505]

Hi Frank,

Thanks a lot.

Type of caller is a pick list which has multiple values.

Family

Self

Teacher

The only filter missing is type of caller. When i applied the filter, i got an error shown in the previous post.

Please let me know if you have any questions.

Thanks,

[marcus505]

Hi Frank,

I was applying the filter by label "Family". As you can see below (this is Dynamics CRM)

So in that case i would need to filter TypeOfCaller by the fieldvalue and sum them i take it rather than Label?

Thanks

[v-frfei-msft · Accepted solution]

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

[marcus505]

Thanks a lot Frank, Greatly appreciate your help.

I will take it from here.

Thanks,

[GilbertQ]

Hi there

You could try this:

My Measure = CALCULATE(SUM(fabric_swishs[Fabric]), TableName[Month] = "April" && TableName[Year] = "2018" && TableName[Type of Caller] = "email")