Will identical Batch numbers in different Business units be an issue in data modelling/DAX?

[saqib1]

Hi,

A certain industry has different business units (BU) manufacturing similar products. It wants to track and compare the performance of different batches within and among the BUs (intra and inter BU).

The BU Id and the Batch Id columns are numerically numbered from 1,2,3...onwards. But due to some user friendliness issue, the dashboard needs to be such that the Batch Number/Name column also needs to be numerical such that within each BU, the first batch of the year would be numbered 1 then 2 & so on. 

Business Unit Id	Batch Id	Batch Number
1	1	1
1	2	2
1	3	3
2	4	1
2	5	2
2	6	3
3	7	1
3	8	2

The problem is that this way each BU will have identical Batch numbers. Should this be a cause of concern in data modelling/DAX? If yes, then what could be the solution (considering that Batch numbers HAVE to start from 1 for each BU at year start).

Appreciate your time.

[grazitti_sapna · Accepted solution]

Hi @saqib1 

You can create a calculated column named "Year" to extract the year from the "Batch Date" column: 

Year = YEAR('YourTable'[Batch Date])

Create a calculated column named "Batch Number" using the following formula:

Batch Number =
RANKX(
FILTER(
ALL('YourTable'),
'YourTable'[Business Unit Id] = EARLIER('YourTable'[Business Unit Id]) &&
YEAR('YourTable'[Batch Date]) = EARLIER('YourTable'[Year])
),
'YourTable'[Batch Id],
,
ASC,
Dense
)

This way you will achieve you desired output

Hope this will help.

[grazitti_sapna · Accepted solution]

Hi @saqib1 

You can create a calculated column named "Year" to extract the year from the "Batch Date" column: 

Year = YEAR('YourTable'[Batch Date])

Create a calculated column named "Batch Number" using the following formula:

Batch Number =
RANKX(
FILTER(
ALL('YourTable'),
'YourTable'[Business Unit Id] = EARLIER('YourTable'[Business Unit Id]) &&
YEAR('YourTable'[Batch Date]) = EARLIER('YourTable'[Year])
),
'YourTable'[Batch Id],
,
ASC,
Dense
)

This way you will achieve you desired output

Hope this will help.

[saqib1]

Hi @grazitti_sapna Thanks alot for giving your time.

Apologies for late reply, actually the solution provided is not giving the intended results. I was working on the data from few days to see whether there's a mistake from my side. Kindly see below the link of the pbix file.

https://1drv.ms/u/s!AoqwAyWfotQsjQgSbAyaW1EDLYEe?e=0ns1yU 

The BU Id 1 is giving correct result for the batch numbers but BU Ids 2 & 3 are giving incorrect results. I have also given name to BUs. The user should be able to select the BU name and Batch number and consequently able to fetch the data of the respective BU's specific batch Number.

One more thing, if it's in your domain or have knowledge about it, I want the batch dates in UK Date format i.e. DD/MM/YYY. I entered all dates in this format in Excel but the ones starting from Single digit dates i.e. 01-09 always reverse the format in Power BI i.e. 01 July 2022 becomes 07 January 2022 and similarly other such dates. I have tried Using Locale to set date formt to UK but it's also not woring. Can you kindly guide on this also? Maybe this could have had an impact on the BU Ids 2 & 3?

Thanks again for your time 🙂

Really appreciate it

[v-yueyunzh-msft · Accepted solution]

Hi, @saqib1 

I think the @grazitti_sapna answer is right ,it just  group and sort according to the year and BU id, if you just want to group according to the BU id, you can remove the judgment of the year.

Batch Number = RANKX(
FILTER(
ALL('Batching Detail'),
'Batching Detail'[BU Id]= EARLIER('Batching Detail'[BU Id])
),
'Batching Detail'[Batch Id],
,
ASC,
Dense
)

And for your date format , you can use "Using Local":

Also you can use format to convert, for this you can refer to :
Solved: How to configure power query date as dd/mm/yyyy in... - Power Platform Community (microsoft....

excel - Power BI: Convert text (yyyymmdd) to date (dd/mm/yyyy) - Stack Overflow

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[saqib1]

Thanks alot @v-yueyunzh-msft Yes that's correct. I couldn't understand it earlier. btw can the YEAR function fetch data according to Financial Year also (1st Jul- 30th Jun)? If Yes, can you kindly guide how?

The Date format is also correct now. Although I was previously also doing the same thing 🙂