Sum at different period and filtered dates

[Anonymous]

Hi All,

New to power Bi, any help will be much appreciated.

I need to calculate the sum at different period, with filtered values.

Sample source data attached.

https://drive.google.com/file/d/1VW7x2cvD0iJSPSXyuqTNxIg1uprB6_Sh/view?usp=sharing 

pbix file.

https://drive.google.com/file/d/1sTipAJiSL2tmbRzFxYZ6xukMbYGEMCFH/view?usp=sharing

F_ValueAtAug-20 : 400000

Regards,

Raj

Modified the source in SQL to get it working. Ideally, I would like to implement something within power bi if possible.

Not sure it is an elegant solution, but it works 🙂 

Power BI	SQL
-1	+1

SELECT [EntityID]
,[Entity]
,[Period]
,[F_Date]
,[F_Value]
,[Year]
into Staging.[SourceAt2020-08-15]
FROM [Target].[Source]
where Period = '2020-08-15'

--truncate table target.MainTable
DECLARE @TargetPeriod AS date

DECLARE db_cursor CURSOR FOR
SELECT distinct Period from Target.Source
where period > '2020-08-15' order by 1

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @TargetPeriod

WHILE @@FETCH_STATUS = 0
BEGIN
insert into target.MainTable
select EntityID , sum(cast(F_Value as decimal(18,2))) FA , @TargetPeriod Period from Staging.[SourceAt2020-08-15]
where F_Date < @TargetPeriod
group by EntityID
FETCH NEXT FROM db_cursor INTO @TargetPeriod
END

CLOSE db_cursor
DEALLOCATE db_cursor

select S.Entity, Sum(cast(F_Value as decimal(18,2))) F_Value, Max(St.FA) expectedValue
from
Target.Source S
left join target.MainTable ST
on S.EntityID = ST.EntityID and s.Period = st.Period
where s.Period = '2021-06-15'
group by S.Entity

[v-stephen-msft]

Hi @Anonymous ,

Sorry to disturb you...

But did I answer your question ? Please mark my reply as solution. Thank you very much.

Best Regards,
Stephen Tao

[v-stephen-msft]

Hi @Anonymous ,

You need to use ALL function. See the dax formula

Measure = CALCULATE(SUM('Source'[F_Value]),FILTER(ALL('Source'),MONTH([Period])=8&&[F_Date]<=DATE(2021,6,21)))

You said that F_Date should be less than June 21st, but you did not select June 15th in the filter. I don't know if it is my misunderstanding or your omission.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Stephen,

Thanks for taking the time and answering my question, much appreciated.

In the calculation, I can hardcode the Period value to "2020-08-15" but F_Date has to be dynamic.

.ie if the Slicer value is "2021-06-15"  F_date value will be less than selected slicer value.

I got it working by t-SQL below. 

Ideally, I would like to get it working within PowerBi.

Please check the below query, if it can help in creating the measure?

SELECT [EntityID]
,[Entity]
,[Period]
,[F_Date]
,[F_Value]
,[Year]
into Staging.[SourceAt2020-08-15]
FROM [ExcelIntegrationLocal].[Target].[Source]
where Period = '2020-08-15'

--truncate table target.MainTable
DECLARE @TargetPeriod AS date

DECLARE db_cursor CURSOR FOR
SELECT distinct Period from Target.Source
where period > '2020-08-15' order by 1

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @TargetPeriod

WHILE @@FETCH_STATUS = 0
BEGIN
insert into target.MainTable
select EntityID , sum(cast(F_Value as decimal(18,2))) FA , @TargetPeriod Period from Staging.[SourceAt2020-08-15]
where F_Date < @TargetPeriod
group by EntityID
FETCH NEXT FROM db_cursor INTO @TargetPeriod
END

CLOSE db_cursor
DEALLOCATE db_cursor

select S.Entity, Sum(cast(F_Value as decimal(18,2))) F_Value, Max(St.FA) expectedValue
from
Target.Source S
left join target.MainTable ST
on S.EntityID = ST.EntityID and s.Period = st.Period
where s.Period = '2021-06-15'
group by S.Entity