Forum Discussion

MarshP's avatar
MarshP
New Member
7 years ago

Using Perviousday to compare values between days excluding weekends

I'm new to power BI and am trying to understand some of the functions.

 

I want a report that shows the difference in values beween 2 dates but exclude weekends

I have multiple items in multiple categories displaying on a day and want to know if the category was not in the report yestarday but is today (In=1) or is no longer in the report today (Out =-1) or is not in the report on either day Null or 0 over a period of time (last 2 weeks)

I only want to show the data when there has been an increase or decrease in the category

 

The way I have approached it so far:

Count Category = DISTINCTCOUNT(T_Data[Category])

Count Category Previous Day = CALCULATE(DISTINCTCOUNT(T_Data[Category),PREVIOUSDAY(T_Data[Reporting_Date]))

 

Change in Category = IF(ISBLANK([Count Category]),0,[Count Category])-IF(ISBLANK([Count Category Previous Day]),0,[Count Category Previous Day])

 

I would then filter out where "Change in Category" = Null Values or 0 Values

 

This works well except on Monday there are no values from the previous day so everything shows up.

 

Ideally I want a crosstable with Category down the side and date across the top with -1,1 or 0 for each category value against a date:

 MonTueWedThuFriMonTueWedThuFri
 3-Sep-184-Sep-185-Sep-186-Sep-187-Sep-1810-Sep-1811-Sep-1812-Sep-1813-Sep-1814-Sep-18
Item11 -1   1  -1
Item2 1   -1  1 
Item3-1  1  -1   
Item4          
Item5   -1      
Item6     1    
           
Total01-1000001-1

 

Eventually I'd like to turn the table into visuals 1= Green Check; -1 = Red cross, 0 = gray circle

 

Any advice would be appreciated

 

P

3 Replies