Need DAX to Calculate Capture Forecast Version Snapshots by Month Halves

[etane]

Hello.

So, we enter forecast starting at beginning of the month.  And, we make periodic updates throughout the month.  Here's what I need:

1) Calculate current year and current month's first half month version of forecast which is total forecast entered but with last modification before the 15th of the month by product.

2) Calculate current year and current month's second half month version of forecast which is total forecast entered but with last modification date on or before the last day of the month by product.

For example, with the below data set:

The first half month version's forecast total should be 110,000 because the mod date 7/7/2025 is before the 15th of the month.  And, the second half month version's forecast should be 120,000.

Similarly, this is what the result should look like with the test data in the link here:

Thanks.

[FBergamaschi · Accepted solution]

With your clear explanations in all posts, solving DAX issues would be so simple

Done

File at my link

Is this ok now?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[etane]

@FBergamaschi your reply is missing the link.

[FBergamaschi]

File is here:

https://drive.google.com/drive/folders/1CFUFS8z6016bfKeoWU_CrZWqmopuE7PT?usp=sharing

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[etane]

Thanks! @FBergamaschi  I wish I could give you two accepted solutions.  

Further, your measure is easy enough to read that even I am able to add Data_Category filter into the measure hence not needing the filter visual.

[FBergamaschi]

You are welcome, happy to have solved!

[v-saisrao-msft]

Hi @etane,

Have you had a chance to review the solution we shared @FBergamaschi @Selva-Salimi ? If the issue persists, feel free to reply so we can help further.

Thank you.

[Selva-Salimi]

Hi @etane 

first create a table like this:

     Table_1 = DATATABLE ("id", INTEGER,    "status", STRING,    {  { 1, "first half" },  { 2, "second half" }})

dont define any relation between this table and any other tables in your file

and then you can write a measure like this:

Measure Amount := if (selectedvalue (table_1[ID] =1 , calculate (sum( [sum of forecast amount]) , filter (your_table , [product_name] = earlier [product_name] && month([sytem mod stamp]) = month([system mod stamp]) && year([system mod stamp])= year([system mod stamp) && day([system mod stamp]) <= 15 , if (selectedvalue (table_1[ID] =1 , calculate (sum( [sum of forecast amount]) , filter (your_table , [product_name] = earlier [product_name] && month([sytem mod stamp]) = month([system mod stamp]) && year([system mod stamp])= year([system mod stamp) && day([system mod stamp]) >= 15))

you can use the column defined in table_1 for each half in your visual table or slicer

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution ✔️to help the other members find it more quickly.

[etane]

Somehow I get stuck at when writing the measure at the earlier function....

[FBergamaschi]

Hi @etane 

my result

Notes:

1 - I disabled autodate/time

2 - for Product C there is no Second Half Forecast in your data, so it dows not appear

3 - even though I did not use the calendar, I just considered a single calendar with two relationships (one disabled)

File is here:

https://drive.google.com/drive/folders/1CFUFS8z6016bfKeoWU_CrZWqmopuE7PT?usp=sharing

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[etane]

@FBergamaschi  Thanks for your response.

Somethings didn't fit my requirements:
1) I need the creation calendar connected because there's multiple years and months in the working file.  So, I need it to isolate to current year and month.  
2) Second half measure should capture the latest forecast that's entered or updated anytime during the month.  So, for Product C, it should pick up the forecast with the latest mod date as below:

[FBergamaschi]

Hello @etane,

absolutely

1) the modification i did has nothing to do with the solution, sorry I forgot to put it back, now it is bak to original, always at my link

2) I modified as per your request, this point was unclear to me, now it is done.

File is here:

https://drive.google.com/drive/folders/1CFUFS8z6016bfKeoWU_CrZWqmopuE7PT?usp=sharing

PS consider that there is this case

in which, at the latest modification sate in the first half, you have two rows (this time with the same value forecast amount) with two different created date, I took the row of the maximum created date and the maximum mod date

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[etane]

I am sorry I need one more tweak.  My fault because I only have one user in the dataset I provided.  But, the measure you provided doesn't work if there's more than one user.  Would you be able to tweak the measure so that it shows total forecast for all users using the same logic in the measure you provided?

[FBergamaschi]

Hi @etane 

I do not understand what you are asking and I do not have any user column

Can you please exaplin and show an image of what you are asking me to achieve?

Thanks

[etane]

@FBergamaschi 

I added some more data to the test file.  2 more reps and one more column, here's link.

Could you please tweak your formula to accomodate:
1) Additional sales reps

2) Added a category field, please filter for "Field Forecast" 

So, the output should look like this:

Again, apologies for not providing a more thorough test data the first time.