Grouping and moving the filter context between different months period to get information

DAX DAX Calculations
1

Hi All,

This is a follow up from this thread

which @Harsh helped me to find a solution but I have come up with another issue which I am struggling to do.

What am I struggling to do?

  1. Grouping projects into pots
  2. Moving the filter context between periods to get information from one month-end into another month-end.

1 Grouping projects into pots

Thanks to @Harsh I was able to calculate the 3 measures I wanted Lost projects, Confirmed projects and New Projects from one month to the next. I am now trying to group these measures into “Lost”, “Confirmed” and “New Projects” so I can visualize what is happening from one month to the next. Here is a screenshot of what I have calculated and you will see that in table 1 the Lost project revenue amount is 4.7m but when I do the grouping in table 2 the lost project amount is only 4.3m so it is missing a couple of projects e.g UK000665.000 (100,000). I am not sure how to fix so when I group them I can get the correct number. I have used a disconnected table and a measure to try to group and all the measures are in the file attached.

image
image1243×806 204 KB

2 Changing the filter context

The next thing I am trying to do is for example if a project in Nov 2021 was lost in Dec 21 I want to be able to manipulate the filter context so I can populate the reason it was lost i.e Column “Closed Notes” in the pipelinetracker data. For example project UK000556 was Step I in Oct 21, Nov 21 and then became Step D in December meaning it is lost, I want to display all of the lost projects in Dec 21 and along side the projects ,revenue amount and the “Closed notes”

image
image1762×470 70.7 KB

At the moment I think because of the way the measure has been calculated the “Closed Notes” Column is displaying blanks and am really not sure how to fix it. The challenge here is really understanding how to manipulate the filter context around periods and I am struggling.

image
image1233×802 147 KB

Your help will be appreciated.

Thanks

Patrick

Here is attached file.
Pipeline Tracker - Grouping and Finding Closed Notes following period.pbix (263.3 KB)

2

HI All,

Could someone please look into this my query.

Thanks

Patrick

3

Bumping this post for more visibility.

4

@sam.mckay hope you well. I am surprised no one has had a chance to really help me with this. The reason I choose you guys is you used to give me back really quick response but it seems that has change for some reason. It will be good if someone can please have a look. And if it is not possible please let me know.

Thanks

5

Hi @ambepat! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!