Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Nanaki
Helper II
Helper II

Dynamic development Measure

Hi,

I got a Power BI report and I am trying to automate them. 
It contains some measures which calculates the development of the last 2 years. 

One of the Measure written like shown. 

 

ManthaOrderNumberDevelopment =
IF(
    ISFILTERED('Date'[Date].[Date]),
    ERROR("Quickmeasures mit Zeitintelligenz können nur über die von Power BI bereitgestellte Datumshierarchie oder die primäre Datumsspalte gruppiert oder gefiltert werden."),
    VAR __PREV_YEAR =
        CALCULATE(
            DISTINCTCOUNT('Bericht 1'[Manta Order Number]),
            DATEADD('Date'[Date].[Date], -1, YEAR)
        )
    RETURN
        DIVIDE(
            DISTINCTCOUNT('Bericht 1'[Manta Order Number]) - __PREV_YEAR,
            __PREV_YEAR
        )
)


Inserted this measure into a card allows me to filter it manually for each year.
I inserted a column called "Last year" in the same table which changes every next year and includes always the numbers of the last year. 
 
Last Year Filter = If(year(DataLabelings[Date a Year Ago]) = 'Date'[Year],'Date'[Date].[Date])

I tried to use this column as a filter for my card. So every year the development card will change. 
But when I am trying to use this as a filter my card won't filter.
Only when I am using the normal date column it will work.
And then it is only possible to change the filter manually.

I hope you get my problem, because my english isnt the best and describing the problem is really hard, without showing the report. 
2 REPLIES 2
amitchandak
Super User
Super User

@Nanaki , if you want to display last year of selected value

a measure

last year =

year(maxx(allselected('Date'),'Date'[Date]) ) -1

 

also do not use .date . Please mark your date table as date table

 

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

 

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

I started with step one and marked my table as a date table. 
I will continue with the other steps later, but by marking it I lost my hierarchy and now I cant even filter it with my old filter for the seperate year.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.