Date intelligence

[Anonymous]

Hi,

I want to compare my actual result with the past result (1 week difference) to use in a KPI visualization and get the % difference. My measure is the following:

Total Clientes = DISTINCTCOUNT('BLABLA'[Codigo_Cliente])

I tried to create another measure to get the past result:

Total Clientes Anterior = CALCULATE([Total Clientes],DATEADD('BLABLA'[FechaCarga].[Date],-1,MONTH))

But it didnt work. How could I get this number?

Thanks in advance,

IC

[Ashish_Mathur]

Hi,

Build a Calendar Table and create a relationship from the Date column of your base data Table to the Date column of the Calendar Table.  In the Calendar Table, create a Year and Month column with these calculated column formulas

Year = Year(Calendar[Date])

Month = FORMAT(Calendar[Date],"mmmm")

Create 2 slicer - one each for Year and Month.  Select any one Year and Month.  Now write these measures:

Total Clientes = DISTINCTCOUNT('BLABLA'[Codigo_Cliente])

Total Clientes Anterior = CALCULATE([Total Clientes],PREVIOUSMONTH(Calendar[Date])

Hope this helps.

[Anonymous]

Hi,

Could you go step by step in creating a Date table? I really need it to get one big date filter for everything.

I have different datasets with different date keys. One of them only have one date variable, but another one has two dates (product retention: one for start date and another for end date). I wanted to make a main date table that links to all of the datasets.

[Ashish_Mathur]

Hi,

Go to Modelling > New Table and write this formula

Calendar = CALENDAR(MIN(Table1[Date]),MAX(Table1[Date]))

Table1 is that Table which carries the minimum date of all Date column across all your Tables.  For the Table where you have a start and end date, we will need to create a single Date column in the Query Editor.  Thereafter, you can create relationships from the Date column of all Tables to the Date column of the Calendar Table.  I can offer more help, if i get some data to work on and if you can be clear about exactly what you want.

[Greg_Deckler]

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....

Also, check out my Quick Measures that deal with weeks:

https://community.powerbi.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=fals...