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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sky571
Advocate I
Advocate I

Issue with Variation using Time Intelligence when figure 1st period is 0

Hello, 

 

I have one issue with this formula regarding Variation in values between dates , when the 1st period of variation is 0.

 

Table 1 :

Sky571_0-1597928091516.png

 

 

Result with the issue :

Sky571_1-1597928091521.png

 

so , in the 2nd row, the variation between 2016 and 2015 should have been +100 instead of blank.

 

Do you know how to do so with my formulas please :

 

Here is my formulas :

Calculated column :

D-C = Table1[Debit]-Table1[Credit]

 

Measures :

Var value =

IF(

    ISFILTERED('Table1'[EndofFiscalYear]),

ERROR("ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),

    VAR __PREV_YEAR =

        CALCULATE(

            SUM('Table1'[D-C]),

            DATEADD('Table1'[EndofFiscalYear].[Date], -1, YEAR)

        )

    RETURN

        DIVIDE(SUM('Table1'[D-C]) - __PREV_YEAR, __PREV_YEAR)*__PREV_YEAR

)

 

Var % =

IF(

    ISFILTERED('Table1'[EndofFiscalYear]),

ERROR("ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),

    VAR __PREV_YEAR =

        CALCULATE(

            SUM('Table1'[D-C]),

            DATEADD('Table1'[EndofFiscalYear].[Date], -1, YEAR)

        )

    RETURN

        DIVIDE(SUM('Table1'[D-C]) - __PREV_YEAR, __PREV_YEAR)

)

 

Thank you in advance

 

 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@Sky571 - When you start to stray outside the lines of the prescribed paths set forth by the time intelligence functions you start to get wacky results. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

amitchandak
Super User
Super User

@Sky571 , not sure of you checking on end of year. With date table and time intelligence

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

with date and year table

//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

 

https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

 

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Sky571 , not sure of you checking on end of year. With date table and time intelligence

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

with date and year table

//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

 

https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

 

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

Greg_Deckler
Super User
Super User

@Sky571 - When you start to stray outside the lines of the prescribed paths set forth by the time intelligence functions you start to get wacky results. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Dear @Greg_Deckler and @amitchandak ,

 

Thank you for your both answers.

 

I have found a way to do what I want with the different blogs you share.

 

Thank you very much for your inputs !!

 

xx

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors