Using Perviousday to compare values between days excluding weekends

[MarshP]

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:

	Mon	Tue	Wed	Thu	Fri	Mon	Tue	Wed	Thu	Fri
	3-Sep-18	4-Sep-18	5-Sep-18	6-Sep-18	7-Sep-18	10-Sep-18	11-Sep-18	12-Sep-18	13-Sep-18	14-Sep-18
Item1	1		-1				1			-1
Item2		1				-1			1
Item3	-1			1			-1
Item4
Item5				-1
Item6						1
Total	0	1	-1	0	0	0	0	0	1	-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

[v-danhe-msft]

Hi @MarshP,

Due to I could not reproduce your data structure, you could use the unichar function and refer to below measures:

The measure about "visuals 1= Green Check":

Measure = IF(CALCULATE(MAX('Table1'[Mon]))=1,UNICHAR(10003))

Set the conditional formatting

Measure about  "0 = gray circle":

Measure2 = IF(CALCULATE(MAX('Table1'[Thu]))=BLANK(),UNICHAR(9898))

Measure about "-1 = Red cross":

Measure 3 = IF(CALCULATE(MAX('Table1'[Wed]))=-1,UNICHAR(10005)) 

And you also need to set the conditional formatting.

Result:

You could also refer to below link and download the pbix file to have a view.

https://exceleratorbi.com.au/dax-unichar-function-power-bi/

https://www.vertex42.com/ExcelTips/unicode-symbols.html

Regards,

Daniel He

[MarshP]

@v-danhe-msftThanks for this! I will definately be adding this once I resolve my issue with Monday comparing to Sunday and not Friday.

This is really helpful for the next step.

P

[v-danhe-msft]

Hi @MarshP,

It's pleasant that your problem could be solved, could you please mark my reply as Answered?

Regards,

Daniel He