Isfiltered in combination with calculated column and different fiscal year

[yakamoz007]

I have to calculate cumulative Key figures.

The fiscal year starts with March 2021 and ends with February 2021.

When I select April 2021, the code should calculate just the month March and April. But it calculates also January and February.

GJ/Monat

Sonntag, 1. März 2020

Mittwoch, 1. April 2020

Freitag, 1. Mai 2020

Montag, 1. Juni 2020

Mittwoch, 1. Juli 2020

Samstag, 1. August 2020

Dienstag, 1. September 2020

Donnerstag, 1. Oktober 2020

Sonntag, 1. November 2020

Dienstag, 1. Dezember 2020

Mittwoch, 1. Januar 2020

Samstag, 1. Februar 2020

Samstag, 1. Februar 2020

Montag, 1. März 2021

Donnerstag, 1. April 2021

Samstag, 1. Mai 2021

Dienstag, 1. Juni 2021

Donnerstag, 1. Juli 2021

Sonntag, 1. August 2021

Mittwoch, 1. September 2021

Freitag, 1. Oktober 2021

Montag, 1. November 2021

Mittwoch, 1. Dezember 2021

Freitag, 1. Januar 2021

Montag, 1. Februar 2021

Montag, 1. Februar 2021

How can I make the coding? My code is not working. GJ/ Monat is my date dimension.

Here is the code one of my calculated column:

IST (kumuliert) =

IF(

    ISFILTERED('Datum Hierarchie'[GJ/Monat]),

    ERROR,

    calculate(TOTALYTD(

        SUM('IstPlan_basetable'[IST]),

        'Datum Hierarchie'[GJ/Monat].[Date]

    )

))

I have also dates for the previous Year and have the same problem.

VJ =

IF(

    ISFILTERED('Datum Hierarchie'[GJ/Monat]),

    ERROR,

    calculate(sum('IstPlan_basetable'[IST]), DATEADD(

        'Datum Hierarchie'[GJ/Monat].[Date],-1,YEAR

)) )

Can someone help me out?

Best regards,

DZ

[amitchandak]

@yakamoz007 , prefer to use date table , do not use date hierarchy in time intelligence as far as possible

example

IST (kumuliert) =
IF(
ISFILTERED('Datum Hierarchie'),
ERROR,
calculate(TOTALYTD(
SUM('IstPlan_basetable'[IST]),
'Datum Hierarchie'[Date] , "2/29"
)
))

Add year end date 

other example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"2/29")) // or use 2/28
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"2/28"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[yakamoz007]

Thanks for your post, but I wont get cumulative numbers.

When I filter April 2021, it returns just the month April 2021. I need March + April 2021.

What do I wrong?

[v-jingzhang]

Hi @yakamoz007 

Did you use a measure or a calculated column? If you used a calculated column, you can check the result directly in the table. If you used a measure, which visual did you put it in?

Regards,
Community Support Team _ Jing

[yakamoz007 · Accepted solution]

Thank you both. I solved the problem by myself. For everybody who has the same problem.

Step 1: Put a calendar table in your data model. There are so much codes. I used this.

Calendar =

VAR Mycal =

    CALENDAR(DATE(2019,3,1), DATE(2022,2,30))

VAR FullCal =

ADDCOLUMNS(

Mycal,

"Index",MONTH([Date]),

"Month",FORMAT([Date],"mmm"),

"Year",FORMAT([Date],"yyyy"),

"YearMon",FORMAT([Date],"mmm-yy"),

"Month Year Sort Order", YEAR([Date]) & UNICHAR(MONTH([Date])+64 )

)

RETURN

FullCal

Step 2:

Put 2 columns in your calendar table (GERMAN language) with this code (if you use the english version your calendar will use english months. In my example I use the german version):

Month real =

if('Calendar'[Month] = "Jan", "März",

if('Calendar'[Month] = "Feb", "April",

if('Calendar'[Month] = "Mrz", "Mai",

if('Calendar'[Month] = "Apr", "Juni",

if('Calendar'[Month] = "Mai", "Juli",

if('Calendar'[Month] = "Jun", "August",

if('Calendar'[Month] = "Jul", "September",

if('Calendar'[Month] = "Aug", "Oktober",

if('Calendar'[Month] = "Sep", "November",

if('Calendar'[Month] = "Okt", "Dezember",

if('Calendar'[Month] = "Nov", "Januar",

if('Calendar'[Month] = "Dez", "Februar", "error"))))))))))))

Step 3:  Change all your input tables and add the columns "date fake" and "date real".

The date in my calendar table is my "date fake". I used my month march as januar. I changed the calculation logic in BI. Because there is no possibility to calculate the cumulative values from Fiscal year March 2020 to Feb 2021. I uesed this trick and it works.

­Value table                                         

Geschäftsj./Periode    date fake         date real          Mon/Year        Values in EUR

001.2021                     01.01.2021      01.03.2021      MAR 2021      100

002.2021                     01.02.2021      01.04.2021      APR 2021       40

003.2021                     01.03.2021      01.05.2021      May 2021        50

004.2021                     01.04.2021      01.06.2021      JUN 2022        45

005.2021                     01.05.2021      01.07.2021      JUL 2021        876

006.2021                     01.06.2021      01.08.2021      AUG 2021      445

007.2021                     01.07.2021      01.09.2021      SEP 2021        545

008.2021                     01.08.2021      01.10.2021      OCT 2021       78

009.2021                     01.09.2021      01.11.2021      NOV 2021      545

010.2021                     01.10.2021      01.12.2021      DEC 2021       45

011.2021                     01.11.2021      01.01.2022      JAN 2021        78

012.2021                     01.12.2021      01.02.2022      FEB 2021        8

013.2021                     01.01.2022      01.02.2022      FEB 2021        78

This is how my value table looks like. I join my "fake date" in the value table with my "date" in my calendar table. BI thinks that the 1.1. is the first and the 31.12. is the last date. I use the 1.1.2021 as my 1.3.2021 (change jannuary with march). So my first month is March. When I add the column "Real month" in my calendar table I can get my real Month. This column I use in my filter which the users can select the real month.

This is a short explanation. I hope it helps everybody who has the same problem.

For my formula to calculate the cumulative values, I used this formula. There is a ISFILTERED code because I use a filter in my report where u can select the month and see the cumulative values.

IST (cumulative) =

IF(

ISFILTERED('Calendar'[Date]),

ERROR(""),

calculate(

SUM('Value_basetable'[IST]),DATESYTD(

'Calendar'[Date]

)

))

If anyone has questions ask me. I have read so much in blogs, forums and everywhere and didn`t find a solution. This is my solution for all. This works! 😉 I have no time to write a blog about that, but I learned so much!