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.
Hi All,
Need some urgent help with the following data.
SLS_PERSON_CD | SLSMAN_NAME | OOS Event | VISIT_DT |
111001 | Richard | No | 15/Feb/16 |
111001 | Richard | Yes | 5/Feb/16 |
111001 | Richard | No | 1/Feb/16 |
111001 | Richard | No | 24/Jan/16 |
111001 | Richard | Yes | 24/Jan/16 |
111001 | Richard | No | 11/Jan/16 |
I need to display the solution in Power BI dashboard as
SLS_PERSON_CD | SLSMAN_NAME | OOS Count (Current Month) | OOS Count (Previous Month) |
111001 | Richard | 1 | 1 |
I need a filter for the month for the dashboard so that if user selects "February", OOS Count should get displayed for January and February both.
The formulas used are:
OOS Count (Current Month) = TOTALMTD(CALCULATE(COUNTROWS('Distribution by Channel PC'), 'Distribution by Channel PC'[OOS Event] = "Yes"), 'Distribution by Channel PC'[Visit Date] )
OOS Count (Previous Month) = CALCULATE(COUNTROWS('Distribution by Channel PC'), 'Distribution by Channel PC'[OOS Event] = "Yes", PARALLELPERIOD('Distribution by Channel PC'[Visit Date], -1, MONTH))
ISSUE: The above formulas are giving me correct result but the constraint is in selection of filters. If I select "February" , OOS COUNT (Previous Month) is empty. If I multi-select the filter "January & February", both the columns give me correct result.
ASK: Can I display OOS COUNT (Previous Month) by having only "February" as my selection in the filter?
Solved! Go to Solution.
Hi @cahghr,
Perhaps you can try to use below formulas to get the current month count and previous month count.
Calendar table:
Date = VALUES(Sheet1[VISIT_DT])
Measure to get selected value:
Select = IF(HASONEVALUE('Date'[VISIT_DT]),VALUES('Date'[VISIT_DT]),BLANK())
CountEvent(Current) = CALCULATE(COUNT(Sheet1[OOS Event]),FILTER(ALLSELECTED(Sheet1),FORMAT([VISIT_DT],"mm/yyyy")=FORMAT([Select],"mm/yyyy")&&[OOS Event]="Yes")) CountEvent(Previous) = CALCULATE(COUNT(Sheet1[OOS Event]),FILTER(ALLSELECTED(Sheet1),FORMAT([VISIT_DT],"mm/yyyy")=FORMAT(DATE(YEAR([Select]),MONTH([Select]),1)-1,"mm/yyyy")&&[OOS Event]="Yes"))
Regards,
Xiaoxin Sheng
Hi @cahghr,
Perhaps you can try to use below formulas to get the current month count and previous month count.
Calendar table:
Date = VALUES(Sheet1[VISIT_DT])
Measure to get selected value:
Select = IF(HASONEVALUE('Date'[VISIT_DT]),VALUES('Date'[VISIT_DT]),BLANK())
CountEvent(Current) = CALCULATE(COUNT(Sheet1[OOS Event]),FILTER(ALLSELECTED(Sheet1),FORMAT([VISIT_DT],"mm/yyyy")=FORMAT([Select],"mm/yyyy")&&[OOS Event]="Yes")) CountEvent(Previous) = CALCULATE(COUNT(Sheet1[OOS Event]),FILTER(ALLSELECTED(Sheet1),FORMAT([VISIT_DT],"mm/yyyy")=FORMAT(DATE(YEAR([Select]),MONTH([Select]),1)-1,"mm/yyyy")&&[OOS Event]="Yes"))
Regards,
Xiaoxin Sheng
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 |
---|---|
111 | |
101 | |
76 | |
64 | |
63 |
User | Count |
---|---|
141 | |
105 | |
101 | |
80 | |
67 |