CODEWITHSUNDEEP
powerbidax
Jonas
Jonas

Reputation: 193

How to get values in PowerBI between two dates in two different columns

Hi i have an issue i cant fix in PowerBI i dont understand DAX that mutch. I have a half solution in DAX and an example what i have tryed. I have the solution in SQL.

WANTED RESULT

I need to get the "time" result summed up that have values between the two selected values. IMPORTANT if one rows of values starts before AND after the selected values then the operation was active that time and shall be included.

enter image description here

@sdate = '2020'

@sdate = '2021'

Select *
From @temp
where (datepart(year,startdate) <= @sdate and datepart(year,enddate))
or (startdate between @sdate and @edate)
or (enddate between @sdate and @edate)

If i do it in SSMS i get the right rows

But in PowerBI i have some issue

I need to be able to choose två year

This part work but not the full solution I just get row 3 and 4 as it sould of this solution.

UPDATED NOW I DONT EVEN GET THIS PART RIGHT enter image description here enter image description here

I want all the green to include and exclude the red

Antal (under året) =
var SelectedYearStart = CONVERT(SELECTEDVALUE(TEST[startdate].[Year]), INTEGER)
var SelectedYearEnd = CONVERT(SELECTEDVALUE(TEST[enddate].[Year]), INTEGER)
return CALCULATE(SUM(TEST[time]),ALLCROSSFILTERED(TEST),year(TEST[startdate])<=SelectedYearStart , year(TEST[enddate])>=SelectedYearEnd)

My guess was this

Antal (under året) =
var SelectedYearStart = CONVERT(SELECTEDVALUE(TEST[startdate].[Year]), INTEGER)
var SelectedYearEnd = CONVERT(SELECTEDVALUE(TEST[enddate].[Year]), INTEGER)
return CALCULATE(SUM(TEST[time]),ALLCROSSFILTERED(TEST),year(TEST[startdate])<=SelectedYearStart , year(TEST[enddate])>=SelectedYearEnd || DATESBETWEEN(TEST[startdate],SelectedYearStart,SelectedYearEnd || DATESBETWEEN(TEST[enddate],SelectedYearStart,SelectedYearEnd)))

But then i get this error

enter image description here

TEST DATA

declare @sdate nvarchar(4)
declare @edate nvarchar(4)
set @sdate = '2020'
set @edate = '2021'
select @sdate sdate
select @edate edate
DEclare @temp table (time decimal(18,2) , startdate date, enddate date)
INSERT INTO  @temp
SELECT 5.0,'2019-01-01','2020-12-01' union all -- 
SELECT 5.0,'2021-01-01','2022-12-01' union all -- 
select 5.0,'2020-01-01','2021-12-01' union all        
select 5.0,'2019-01-01','2022-12-01' union all --
select 5.0,'2019-01-01','2019-12-01' union all --
select 5.0,'2022-01-01','2022-12-01' union all
select 5.0,'2020-01-01','2020-12-01' union all
select 5.0,'2021-01-01','2021-12-01'
--select 5.0,'2020-01-01','3000-01-01' --EXTRA
SELECT *
into TEST
FROM @temp
--ORDER BY startdate,enddate

Upvotes: 0

Views: 520

Answers (1)

Joao Leal
Joao Leal

Reputation: 5542

You need something like this:

var SelectedYearStart = SELECTEDVALUE(TEST[startdate].[Year])
var SelectedYearEnd = SELECTEDVALUE(TEST[enddate].[Year])
return CALCULATE(SUM(TEST[time]),
        ALL ( TEST[startdate].[Year]),
        ALL ( TEST[enddate].[Year]),
        KEEPFILTERS(
               (Year(TEST[startdate]) <= SelectedYearStart && 
                Year(TEST[enddate]) >= SelectedYearEnd)
            || (Year(TEST[startdate]) >= SelectedYearStart && 
                Year(TEST[startdate]) <= SelectedYearEnd) 
            || (Year(TEST[enddate]) >= SelectedYearStart && 
                Year(TEST[enddate]) <= SelectedYearEnd))
        )

First you need to clear the filters on the table that are created via the two slicers (startdate.Year and enddate.Year)

Then you need to pass in your complex filter query. Note that I'm using the Year(..) function because you can't reference columns from different tables in the filter section of the Calculate (and startdate.year and enddate.year are coming from two separate date tables created automatically by powerbi).

Finally you need to wrap that into a KEEPFILTERS statement to ensure that only the current row context is being applied to the expression.

Upvotes: 1

Related Questions