Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
@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
Hi @MarshP,
It's pleasant that your problem could be solved, could you please mark my reply as Answered?
Regards,
Daniel He
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |