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.
Dear Guru/s,
I am a novice in Power Bi desktop - learning the ways.
Have been trying to build a report based on slicer selection of Month / Location/ Status/ & Type.
Able to create MTD avg and %ages but slicers do not work in accordance to moving avg YTD or Total YTD exits - PBIX link attached.
This is a single table mentioned below :
Date | Zone | Entity_Desc | Ids | Type | Status | Unit | Status | Exits | DateKey | YearNumber | Year | MonthNumber | Month |
Apr-19 | NCR-1 | Discovery Network | XX01 | Employee | Full Time | LOS | Opening |
| 20190401 | 2020 | FY 2020 | 1 | April |
Apr-19 | NCR-1 | Walmart | XX02 | Temporary | Part Time | HOU | Opening |
| 20190401 | 2020 | FY 2020 | 1 | April |
Apr-19 | NCR-1 | Holiday Inn | XX03 | Contractual | Full Time | HOU | Opening | Inactive | 20190401 | 2020 | FY 2020 | 1 | April |
May-19 | NCR-1 | Discovery Network | XX01 | Employee | Full Time | LOS | Opening |
| 20190501 | 2020 | FY 2020 | 2 | May |
May-19 | NCR-1 | Walmart | XX02 | Temporary | Part Time | HOU | Opening | Inactive | 20190501 | 2020 | FY 2020 | 2 | May |
May-19 | NCR-1 | Holiday Inn | XX04 | Employee | Nursing | TEX | New joiner |
| 20190501 | 2020 | FY 2020 | 2 | May |
Jun-19 | NCR-1 | Discovery Network | XX01 | Employee | Full Time | LOS | Opening |
| 20190601 | 2020 | FY 2020 | 3 | June |
Jun-19 | NCR-1 | Walmart | XX04 | Temporary | Part Time | TEX | Opening | Inactive | 20190601 | 2020 | FY 2020 | 3 | June |
Jun-19 | NCR-1 | Holiday Inn | XX05 | Contractual | Full Time | HOU | New joiner | Inactive | 20190601 | 2020 | FY 2020 | 3 | June |
Jul-19 | NCR-1 | Discovery Network | XX01 | Employee | Nursing | LOS | Opening |
| 20190701 | 2020 | FY 2020 | 4 | July |
Jul-19 | NCR-1 | Walmart | XX06 | Contractual | Full Time | TEX | New joiner |
| 20190701 | 2020 | FY 2020 | 4 | July |
Solved! Go to Solution.
@Anonymous -
Here are measures
Exits = CALCULATE(COUNTROWS(Sheet1),FIND("Inact",Sheet1[Exits],,0))
//copied from your file
YTD Exits = CALCULATE([Exits],DATESYTD(Sheet1[Date]))
MTD AVG HC = ('Measure'[Opening_HC]+'Measure'[Closing HC])/2
//copied from your file
YTD AVG HC = CALCULATE([MTD AVG HC],DATESYTD(Sheet1[Date]))
Output
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
@Anonymous - Use ALLEXCEPT() instead of ALL().
Reference link: https://excelkingdom.blogspot.com/2018/08/how-to-use-all-and-allexcept-functions.html
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Thanks @nandukrishnavs, the ALLEXCEPT function did the trick partially.
As the report is now responding to Slicers - but the issue remains as the YTD total exits are showing in each row- and not as a YTD running total.
Please find below the screen shot of both YTD exits (running total) as against the 2YTD Exits (using AllExcept) :
The DAX formulae used for :
Exits
Hi @ nandukrishnavs,
Really grateful to you for suggesting, this works!!
Now i get the exits for the month, but not cumulative i.e - April has1, May also has 1 - hence May YTD should reflect as 1+1 = 2.
Please Suggest,
Thanks again in advance,
Pratz
Hi @nandukrishnavs,
Thanks, sure.
Please find below the snapshot of FYYear and Month - N_YTD exits is the header.
Please suggest,
Thanks again for your advise.
Regards,
Pratz
@Anonymous -
Here are measures
Exits = CALCULATE(COUNTROWS(Sheet1),FIND("Inact",Sheet1[Exits],,0))
//copied from your file
YTD Exits = CALCULATE([Exits],DATESYTD(Sheet1[Date]))
MTD AVG HC = ('Measure'[Opening_HC]+'Measure'[Closing HC])/2
//copied from your file
YTD AVG HC = CALCULATE([MTD AVG HC],DATESYTD(Sheet1[Date]))
Output
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Thanks a Ton @nandukrishnavs !!!. so patiently you have adressed the concerns.
Yes, the solution you had provided was working fine, only i got confused in selecting the month and Year seperately and not the date.
Thank you once again 🙂
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |