Forum Discussion
If Date = x Then select value y
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!
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.
3 Replies
- amitchandak
Super User
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
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
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.
