Calcualted Column to flag cokpleted months in fact table

[erikmi]

I need a calcualted column that I can use as a slicer to eahter only schow revenue for completed months ore to schow all the revenue. 

I have following tables:

FactRevenue: ProjectID| Date| ActualRevenue|ActualProduction
Calendar: Date|Month|.....

Several Dimensions

The calculated column should check for ech projectID if there is a value ind the revenue AND in the production column for a given Date. If there is a value for both Production and Revneu in for a Project at the given date , the column should return "completed", otherwise it should return "incomplete"

[erikmi]

I think your solution works the way I need it to, only problem is I need it to work as a Slicer, so it needs to be a calculated column.

[v-tangjie-msft · Accepted solution]

Hi @erikmi ,

Please try this :

Column = 
IF('FactRevenue'[ActualProduction]<> BLANK() &&'FactRevenue'[ActualRevenue] <> BLANK(),"completed","incompleted")

Best Regards,

Neeko Tang

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

[erikmi]

@v-tangjie-msft 
Looks good. Thank you!

Now I need to add a condition that checks if there is data for every Porject, menaing does every project exist in specific month 

[v-tangjie-msft]

Hi @erikmi ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create two measures.

Measure = 
var _a =SELECTEDVALUE('CALENDAR'[Date])
return 
IF(MAX('FactRevenue'[Date])=_a && MAX('FactRevenue'[ActualProduction]) <> BLANK() && MAX('FactRevenue'[ActualRevenue]) <> BLANK(),"completed","incompleted")

Measure 2 = var _a =SELECTEDVALUE('CALENDAR'[Date])
var _b =CALCULATE(MAX('FactRevenue'[Date]),FILTER('FactRevenue','FactRevenue'[Date]))
return 
IF(_b=_a ,1,0)

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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