Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
tmendoza
Helper III
Helper III

How to show amounts for period that has been filtered out

Hello developers,

 

I'm in need of some DAX assistance.

I'm trying to create a dashboard that reflects Head Count. It's almost complete, but I need to reflect a starting head count for periods selected in my slicer. Or more logicaly explained, Head Count for the period prior to the one selected.

Head Count and Fiscal Period are in my Fact Table whilst Job Title is in my Dim table. The relationship between the two tables is linked by employee key and looks like this:

tmendoza_4-1663968187393.png

 

My fiscal periods are expressed with Fiscal Year first then the Fiscal Period. ex. 202301 = First Period in Fiscal Year 2023.

So far, the below DAX has been working out for me:

Starting HC =
CALCULATE(
SUM(FactEmployeeFiscal[Active]),
FILTER(ALL(FactEmployeeFiscal[FiscalPeriod]),FactEmployeeFiscal[FiscalPeriod]),
FactEmployeeFiscal[RowNum]=MAX(FactEmployeeFiscal[RowNum])+1)

 

RowNum or Row Number is an index for the Fiscal Period with the Max period = 1. Currently the highest Fiscal Period available is 202305.

202305 = 1; 202304 = 2; 202303 = 3 etc.

 

The trouble starts when I filter to only FY2023 and select the first Fiscal Period. My results go blank.

Starting Head Count needs to be the headcount for the period prior to the one being selected. In this case when I select 202301, my measure looks for the headcout to 202212. But FY22 is filtered out, so the column goes blank.

tmendoza_2-1663967714891.png

I've switched up my formula to update the ALL comand to this:

Starting HC =
CALCULATE(
    SUM(FactEmployeeFiscal[Active]),
       FILTER(ALL(FactEmployeeFiscal),FactEmployeeFiscal[FiscalPeriod]),
    FactEmployeeFiscal[RowNum]=MAX(FactEmployeeFiscal[RowNum])+1)
The total amount is right and it's a step in the right direction, but I'm getting the same amount for every row.
tmendoza_3-1663967967721.png

Do any of you geniouses out there know what I need to do to my DAX measure to accurately reflect HC for the first Fiscal Period in FY2023?

 

Thanks for any Help!!!

 

 

2 REPLIES 2
amitchandak
Super User
Super User

@tmendoza , You need to have a separate fiscal period table . You need the sortable continuous column for period

 

Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)// Use FY Period RANK

 

Starting HC Last Period =
CALCULATE(
    SUM(FactEmployeeFiscal[Active]),
       FILTER(ALL(dimFiscal), 
    dimFiscal[Period Rank]=MAX(FactEmployeeFiscal[Period Rank])- 1)

 

 

 

refer

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hello @amitchandak , Thanks for the Reply!

 

You are pretty darn close, but it's not quite there. Although, looking at it, your DAX seems solid.

 

I added an additional period table and gave it a rank column. The Max period = 1. I also joined it to FactEmployeeFiscal by FiscalPeriod. 

tmendoza_1-1664574700184.png

tmendoza_3-1664574824023.png

 

The Measure is as follows:

tmendoza_2-1664574793628.png

You are close, but the result reflects the starting headcount for the period selected rather than for the period prior. In the pic below, the starting HC should be 3,024. 

tmendoza_4-1664574911391.png

I've changed your -1 to a +1 and tried -2 and +2, but the result remains the same.

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.