cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ajohn1 Regular Visitor
Regular Visitor

Column value as Measure value

Lets say my date column has the value of '2018-12-27'.  How can I get my measure (not column) to equal exacly '2018-12-27'. Or how can I get my measure to equal just the year '2018'.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
mnayar Established Member
Established Member

Re: Column value as Measure value

@ajohn1 how about calculate number of days by doing a countdistinct on number of dates in the date table for a year

 

for example

 

Customer Count =

var numdays = CALCULATE(distinctcount([date]), allexcept(query1,year(query1[targetdate]))

return sum(Query1[Customer Served])/ numdays

5 REPLIES 5
mnayar Established Member
Established Member

Re: Column value as Measure value

@ajohn1 can you give an exact scenario when you are trying to do this?

ajohn1 Regular Visitor
Regular Visitor

Re: Column value as Measure value

Calculated column Total_Days_in_Year  =  Date(year(Query1[TargetDate]),12,31)-DATE(year(Query1[TargetDate]),1,1)+1. Total_Days_in_Year can either be 365 or 366(leap year). This returns the right value.

 

I have a calculated measure ‘Customer Count = sum(Query1[Customer Served]) / ( ??? *4)’. I would like to replace ??? with Total_Days_in_Year. Power BI will only let me use Total_Days_in_Year if it is aggregated.

 

So what can I do here?

Highlighted
mnayar Established Member
Established Member

Re: Column value as Measure value

@ajohn1 how about calculate number of days by doing a countdistinct on number of dates in the date table for a year

 

for example

 

Customer Count =

var numdays = CALCULATE(distinctcount([date]), allexcept(query1,year(query1[targetdate]))

return sum(Query1[Customer Served])/ numdays

ajohn1 Regular Visitor
Regular Visitor

Re: Column value as Measure value

Thans so much it worked. Where can I go to learn more things like this?

mnayar Established Member
Established Member

Re: Column value as Measure value

@ajohn1 i am still learning a lot of this stuff and I am doing it by solving problems on the forum.

 

also could you mark the correct solution as the accepted one?