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:
#LastMonth = CALCULATE(MAX(Datos[Valor]);PREVIOUSMONTH(Calendario[Fecha]))
Solved! Go to Solution.
When using date tables to invoke time intelligence in DAX there are three fundamental principles that must always be applied.
Can you confirm all three are being followed?
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!
@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.
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:
Previous Month = CALCULATE(SUM('Fact'[Amount]),PREVIOUSMONTH('Calendar'[Date]))
Last Week Column = CALCULATE(SUM('Fact'[Amount]),DATEADD('Calendar'[Date],-7,DAY))