Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 wo...
Need Help
Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
9 years ago

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! :smileyhappy:

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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!