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
Anonymous
Not applicable

Problem with Time Intelligence functions

 

Hi Everyone!

 

I have a table with data with one date and one value by row. There is one value for week.
I have created My Calendar Table but when I use the Time Intelligence Formulas didn't work.

 

I Can't show mesures like:
#LastWeek= CALCULATE(MAX((Datos[Valor]);DATEADD(Calendario[Fecha];-7;DAY))

#LastMonth = CALCULATE(MAX(Datos[Valor]);PREVIOUSMONTH(Calendario[Fecha]))

Any Help?
Thanks

Captura.PNG

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thanks for your suggestion @v-sihou-msft.

 

I was looking for a solution and I found this article that gave me the way.

http://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

 

View solution in original post

5 REPLIES 5
SqlJason
Memorable Member
Memorable Member

When using date tables to invoke time intelligence in DAX there are three fundamental principles that must always be applied.

  • The date range must be continuous in the Date tableThat is there must not be any dates missing in the column that contains the list of calendar days in the table of dates.
  • The date range in the Date table must encompass all the datesthat you will be using in other tables in the data model.
  • For time intelligence in Power BI Desktop to work correctly the fields used to join a date table and a data table must both be set to the date or datetime data type.

Can you confirm all three are being followed?

Sean
Community Champion
Community Champion

In addition to what @SqlJason said...

 

The PREVIOUSMONTH function will not give you anything if you don't have context (a date field in the Visual)

For example if you put in a Card or a table by itself you'll get nothing

 

I would suggest the following:

 

1) Add this COLUMN to your Calendar Table

Month Order =
INT (
    CONCATENATE (
        YEAR ( Calendario[Fecha] );
        CONCATENATE (
            IF ( MONTH ( Calendario[Fecha] ) < 10; "0"; "" );
            MONTH ( Calendario[Fecha]; )
        )
    )
)

2) Then heres's Measure 1

#LastWeek =
CALCULATE (
    MAX ( Datos[Valor] );
    DATESINPERIOD ( Calendario[Fecha]; LASTDATE ( Calendario[Fecha] ); - 7; DAY )
)

3) And Measure 2

#LastWeek =
CALCULATE (
    MAX ( Datos[Valor] );
    FILTER (
        ALL ( Calendario );
        Calendario[Month Order]
            = MAX ( Calendario[Month Order] ) - 1
    )
)

Hope this helps! Smiley Happy

Anonymous
Not applicable

Hello!

 

@SqlJason- In answer to your question:Yes, my data meets all three rules.

@Sean- Thanks for your proposed but I still have the problem.

 

I think the problem could be the fact that when I filter the data by one product, I only have one date per week but It didn't have sense.

 

I continue looking for the solution.
Thanks!

 

@Anonymous

 

The DATEADD() function only works for contiguous date selection, which means you need to select contiguous dates into your table visual. In this scenario, you can create a calculated column instead of a measure to workaround this issue.

 

For PREVIOUSMONTH() function, it should work once you create a relationship between your fact table and the full calendar table.

 

See my sample below:

 

6.PNG

 

Previous Month = CALCULATE(SUM('Fact'[Amount]),PREVIOUSMONTH('Calendar'[Date]))
Last Week Column = CALCULATE(SUM('Fact'[Amount]),DATEADD('Calendar'[Date],-7,DAY))

 

 

 

7.PNG

 

Regards,

Anonymous
Not applicable

Thanks for your suggestion @v-sihou-msft.

 

I was looking for a solution and I found this article that gave me the way.

http://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

 

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.