cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

MTD and YTD headcount with dynamic slicers

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. 

Sample file 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@Pratz -

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

o.JPG

 

p.JPG

 



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂

View solution in original post

9 REPLIES 9
Highlighted
Super User III
Super User III

@Pratz - 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
🙂

Highlighted

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) :

Capture.PNG

 

 

The DAX formulae used for :

 

Exits  

= CALCULATE(COUNTROWS(Sheet1),FIND("Inact",Sheet1[Exits],,0))
 
YTD Exits
= (CALCULATE([Exits],
FILTER(
ALL(Sheet1),
Sheet1[Year] = MAX (Sheet1[Year])
&& Sheet1[DateKey] <= MAX(Sheet1[DateKey])
)
))
 
2YTD Exits
= CALCULATE([Exits],
ALLEXCEPT(Sheet1,Sheet1[Entity_Desc],Sheet1[Status],Sheet1[Zone],Sheet1[Type])
)
 
Please suggest.
 
Regards,
Pratz
Highlighted

@Pratz 


Try this

 

YTD Exits = CALCULATE([Exits],DATESYTD(Sheet1[Date]))

 

Highlighted

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

 

Highlighted

Can you add [Date], [Exits], [YTD Exits] into the table and share the snapshot?
Highlighted

Hi @nandukrishnavs,

Thanks, sure.

Please find below the snapshot of FYYear and Month  - N_YTD exits is the header.

Capture_2.PNG

Please suggest,

Thanks again for your advise.

 

Regards,

Pratz

Highlighted

I can see, May YTD is 2. Also i couldn't understand your ask. For me YTD values are correct.
Highlighted

@Pratz -

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

o.JPG

 

p.JPG

 



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂

View solution in original post

Highlighted

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 🙂

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors