Measure from calculated column

[Anonymous]

Below are screenshots from my Calendar table. Today is February 20, 2019, so in the IsToday calculated column, everything is 0 except for today, which is a 1. My ultimate goal is to create a measure that has a logical structure of, for example, IF(IsToday = 1 && FinancialWeekofQuarter = 8, then do some calculation, assuming we are using today's date of Feb 20, which corresponds to a FinancialWeekofQuarter value of Week 8.

The way I see it, there are two routes to take: 

1. Create a measure that achieves the same things as the IsToday calculated column 

2. Create a measure that uses the IsToday calculated column 

I've tried both so far but the issue with 1 is that you can't reference the date column in the measure. The issue with 2 is that you can't reference the IsToday calculated column.

So if someone can please help me with one or both approaches, that would be greatly appreciated. Thanks in advance!

[v-juanli-msft]

Hi @Anonymous 

Create two measures

is today = IF(FORMAT(NOW(),"mm/dd/yy")=FORMAT(MAX('Table'[Date]),"mm/dd/yy"),1,0)

logical = IF([is today]=1&&MAX('Table'[week of year])=8,1,0) (you could replace 1/0 with your calcuation)

Today is 2019/2/21

Best Reagrds

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-juanli-msft this works for what you are saying, but if I want to do any calculations in the If-true part, nothing is displayed, as shown in the screenshot. 

Maybe I wasn't precise enough in my first post but I'm looking to have something like this. 

ForecastMaster Orders test = 

var todaynum = WEEKDAY(TODAY(), 2)
var todaynumfinal = IF(todaynum = 6 || todaynum = 7, 1, todaynum)

return
IF([CurrentMonth] = 1 && [CurrentWeek] = "Week 1", ([ForecastMaster Orders] * todaynumfinal)/20,
IF([CurrentMonth] = 1 && [CurrentWeek] = "Week 2", ([ForecastMaster Orders]* (todaynumfinal + 5))/20,
IF([CurrentMonth] = 1 && [CurrentWeek] = "Week 3", ([ForecastMaster Orders]* (todaynumfinal + 10))/20,
IF([CurrentMonth] = 1 && [CurrentWeek] = "Week 4", ([ForecastMaster Orders]* (todaynumfinal + 15))/20))))

Where [CurrentMonth], [CurrentWeek] and [ForecastMaster Orders] are measures as follows:

CurrentMonth = LOOKUPVALUE(Dim_Calendar[FinancialMonthID],Dim_Calendar[IsToday], 1)

CurrentWeek = LOOKUPVALUE(Dim_Calendar[FinancialWeekOfQuarter],Dim_Calendar[IsToday], 1)

ForecastMaster Orders = CALCULATE(SUM(ForecastMaster1[Forecast Value]),ForecastMaster1[Type] = "Orders")

[v-juanli-msft]

Hi @Anonymous 

Could you share a simple pbix file to test?

Just delete privacy data and keep some data that can illustrate the problem.

one way to share data is to uplaod the file to One drive and share the link here.

or paste some example data here.

Best Regards

Maggie

[Anonymous]

@v-juanli-msft 

I think I partly figured it out, I created a summary table grouped by the week and other necessary fields. If I have further questions, I'll create another post and put the link here. Thanks