Time Intelligence - All periods comparison in one DAX formula

[rpattan]

Hi There,

Could we have a single DAX formula for 'Time Intelligence' which may cover all of the below requirement in one shot rather than many while slicing and dicing...!

1. Monthly periods should compare with the previous month.

2. Quarterly periods should compare with the previous quarter.

3. Yearly periods should compare with the previous year.

4. Quarter-to-date periods should compare with the previous quarter.

5. Year-to-date periods should compare with the previous year.

6. Custom date ranges shouldn’t compare with a previous period.

My knowledge is upto building a single criteria based formula. Thanks in advance.

Regards,

Riyaz

[rpattan · Accepted solution]

Hi All,

Please find the solution for this query at below link.

http://community.powerbi.com/t5/Desktop/COMPARISON-Current-Period-sum-Vs-Previous-Period-sum-Change/...

Thanks

[rpattan]

Dear Folks,

Could some one spend a bit of time on the need. THANK YOU.

Riyaz

[rpattan]

Could some one look into the need.

Thanks

Riyaz

[Floriankx]

Hello,

I assume you have a separate DateTable with contigous dates?

for monthly, quartely and yearly periods compared to period before this might work:

Value:=Sum(Table[Values])

Value_PreviousPeriod:=
IF(HASONEVALUE(DateTable[Year]),
 IF(HASONEVALUE(DateTable[Quarter]),
  IF(HASONEVALUE(DateTable[Month]),                //True HOV[Quarter]
   CALCULATE([Value],DATEADD(DateTabe,-1,month),     //True HOV[Month]
   CALCULATE([Value],DATEADD(DateTabe,-1,quarter)),   //False HOV[Month]
  CALCULATE([Value],DATEADD(DateTabe,-1,year)),       //False HOV[Quarter
 Blank())

You could use similar Syntax to create a ToDate Value

Value_PeriodTD:=
IF(HASONEVALUE(DateTable[Year]),
 IF(HASONEVALUE(DateTable[Quarter]),
  IF(HASONEVALUE(DateTable[Month]),                //True HOV[Quarter]
   TOTALMTD([Value],DateTable)                     //True HOV[Month]
   TOTALQTD([Value],DateTable)                     //False HOV[Month]
  TOTALYTD([Value],DateTable)                      //False HOV[Quarter
 Blank())

 You could add another level to check if a single day is selected, but you should try this first.

[rpattan]

@Floriankx

Thank you for commenting.

Could you correct me;

Value_PreviousPeriod =
IF(HASONEVALUE('Date'[Date].[Year]),
IF(HASONEVALUE('Date'[Date].[Quarter]),
IF(HASONEVALUE('Date'[Date].[Month]), //True HOV[Quarter]
CALCULATE([Value],DATEADD('Date'[Date],-1,month), //True HOV[Month]
CALCULATE([Value],DATEADD('Date'[Date],-1,quarter)), //False HOV[Month]
CALCULATE([Value],DATEADD('Date'[Date],-1,year)), //False HOV[Quarter
Blank()))))

Errior Message:

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Thanks

Riyaz

[Floriankx]

Hello,

my bad!

Value_PreviousPeriod =
IF(HASONEVALUE('Date'[Date].[Year]),
IF(HASONEVALUE('Date'[Date].[Quarter]),
IF(HASONEVALUE('Date'[Date].[Month]), //True HOV[Quarter]
CALCULATE([Value],DATEADD('Date'[Date],-1,month)), //True HOV[Month]
CALCULATE([Value],DATEADD('Date'[Date],-1,quarter))), //False HOV[Month]
CALCULATE([Value],DATEADD('Date'[Date],-1,year))), //False HOV[Quarter
Blank())

This should be better.

[rpattan]

@Floriankx

The given formula has no error shown, my pardon that it gives result as blank. Could you spare some more time on this.

https://drive.google.com/file/d/156PBYM7WEDj6thG9jgTGO4n6TsQ0YR55/view?usp=sharing

Ediable pbix is provided. Thanks

Riyaz

[Floriankx]

Hello I'm supporting during work. 

IT restrictions prohibit access to cloud services. I'm sorry.

[rpattan]

@Floriankx

Thank you for your time.

[Greg_Deckler]

Might be possible. Can you give me a sense of your data? What do you have in your Calendar/Date table?

[rpattan]

Hi,

@Greg_Deckler

Here is the data sample. Please to look. Thanks

Title	Badge Title	Badge Type	Badge Given By	Badge Created Date	User Sys ID	Division	COUNT
CoE Manager	Happy Customers	Custom	201000007	7/31/2017 7:16	301000019	Delivery (Del)	1
CoE Manager	Happy Customers	Custom	201000007	7/31/2017 7:16	301000019	Delivery (Del)	1
CoE Manager	Drives Results	Custom	101000506	7/31/2017 8:02	301000019	Delivery (Del)	1
CoE Manager	Collaborates	Custom	101000290	8/3/2017 14:14	301000019	Delivery (Del)	1
CoE Manager	Develops Talent	Custom	101000490	9/15/2017 15:45	301000019	Delivery (Del)	1
CoE Manager	Collaborates	Custom	101000463	10/13/2017 7:43	301000019	Delivery (Del)	1
CoE Manager	Happy Customers	Custom	201000001	10/13/2017 11:12	301000019	Delivery (Del)	1
CoE Manager	Team Player	Custom	101000290	10/13/2017 19:38	301000019	Delivery (Del)	1
CoE Manager	Happy Customers	Custom	201000001	11/15/2017 7:25	301000019	Delivery (Del)	1
CoE Manager	Collaborates	Custom	101000316	11/30/2017 12:40	301000019	Delivery (Del)	1
CoE Manager	Develops Talent	Custom	101000490	12/4/2017 2:04	301000019	Delivery (Del)	1

[rpattan]

@Greg_Deckler

Please consider this link.

https://drive.google.com/file/d/156PBYM7WEDj6thG9jgTGO4n6TsQ0YR55/view?usp=sharing

Thanks

Riyaz

[rpattan]

@Greg_Deckler

Thanks for your valueble time. 

Link:

https://drive.google.com/file/d/1vAexcS1WUHtpR5F3jcGjCMBHLtGBHrju/view?usp=sharing

The link will give you an editable PBIX file. And in the file there are "PoP change" (Period on Period) percentages available, of which the respective percentages are not changing while selecting the month, quarter and year and so on. 

Your suggestion could be an answer to many users like me. 

THANKs

Riyaz