If Date = x Then select value y

[cbhami02]

Hello,

I can't seem to find what I am looking for but basically I need to create a rolling calendar where:

Column A         Column B                      Goal (200)

Day 1                      5%                                 10

Day 2                      10%                                20

Day 3                       15%                               30

If date = day 3, then 200 x 15% = 30

I don't know how to list that in DAX so it is difficult to look up. Thank you!

[Anonymous · Accepted solution]

@cbhami02 

I create a sample pbix, basically if you have a date, you can follow my dax formula.

Rank day = RANKX(FILTER('Table',[Date].[Month]=EARLIER([Date].[Month])),[Date],,ASC)

Percent = [Rank day]/CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Date].[Month]))

Goal = 200 * [Percent]

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@cbhami02 , I am not sure How % is coming.

Usually, if this column and you need rolling then 

day can be date

New Column =

sumx(filter(Table, [Day] <=earlier([Day] ),[Percent]) *200

measure =

sumx(filter(allselecetd(Table), [Date] <=max([Date] ),[Percent]) *200

[cbhami02]

The percent would be the number of work days in the month, so usually 19-20. In this example, 20.

I know how to do a VLOOKUP in excel to have -

Total Mission x (VLOOKUP % based on day of the month(=Today that updates when I open the excel)) = 200*percent = by day 5 of the month, you should be at 50 widgets produced.

Let me try those measures