Forum Discussion
Complex transaction calculation via User controlled date range
Hi All,
I have a problem that I'm unable to solve in PBI. We have used Sisense, where we write SQL queries and pass user input dynamically and use it in where filter clause and case statements.
Problem:
We have a transaction table which has contractor details: contractor_id, Job title, start date, end date and daily cost rate.
Our Business operates in Fiscal calender: July to June time frame.
We need to calculate the contractor cost transaction that happened in each FY or in any date range as selected by the report user.
A contractor may start in one FY and may end in same FY or may end in next FY. So we need to cut off the correct start date and end date to calculate the transaction, according to the FY the user looks for.
Output: Overall transaction needs to represented in a card and will change as per the FY or date range selected by the user.
I have shared the transaction pbix file in onedrive.
SQL For Getting FY21 contractor cost transactions:
These two are variables that can be controlled by the user.
Daterange_start= '2020-07-01',
Daterange_end='2021-07-31'
Select sum(end_date-start_date)*cost_rate as total_transaction_rate
from
(
Select
ts.contractor_id
,ts.job_title
,ts.daily_rate
,case when ja.start_date<[daterange_start] then [daterange_start]
else ja.start_date end as start_date
, case when ja.end_date>[daterange_end] then [daterange_end]
else ts.end_date end as end_date
from transactions ts
where end_date>=[daterange_start]
and start_date<=[daterange_end]
)A
Thanks in advance for any help and your valuable time.
You are welcome. Go to the Query Editor and review the steps in the Applied steps pane. Yes, the file size will increase but i do not know of a better way to solve this.
4 Replies
Ashish_Mathur Thank you for your time! I see that you have selected all the dates between start date and end date and put it in a new column. How did you do that transformation? Also, will this not increase the size of the table as we grow in data? Currenty we have 50K contractors.
You are welcome. Go to the Query Editor and review the steps in the Applied steps pane. Yes, the file size will increase but i do not know of a better way to solve this.
Hi Anonymous ,
If you are trying to sum up values in fiscal year, you can create the following date table and create a one to many relationship to your transcation table:
Calendar = VAR WeekStartsOn = "Mon" VAR FiscalStartMonth = 7 RETURN ADDCOLUMNS ( CALENDARAUTO ( FiscalStartMonth - 1 ), "FiscalMIndex", MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ), "Fiscal Week", VAR FiscalFirstDay = IF ( MONTH ( [Date] ) < FiscalStartMonth, DATE ( YEAR ( [Date] ) - 1, FiscalStartMonth, 1 ), DATE ( YEAR ( [Date] ), FiscalStartMonth, 1 ) ) VAR FilteredTableCount = COUNTROWS ( FILTER ( SELECTCOLUMNS ( GENERATESERIES ( FiscalFirstDay, [Date] ), "Dates", [Value] ), FORMAT ( [Dates], "ddd" ) = WeekStartsOn ) ) VAR WeekNos = IF ( FORMAT ( FiscalFirstDay, "ddd" ) <> WeekStartsOn, FilteredTableCount + 1, FilteredTableCount ) RETURN "Week " & WeekNos, "Fiscal Qtr", "Q" & CEILING ( MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ), 3 ) / 3, "Fiscal Year", VAR CY = RIGHT ( YEAR ( [Date] ), 2 ) VAR NY = RIGHT ( YEAR ( [Date] ) + 1, 2 ) VAR PY = RIGHT ( YEAR ( [Date] ) - 1, 2 ) VAR FinYear = IF ( MONTH ( [Date] ) > ( FiscalStartMonth - 1 ), CY & "-" & NY, PY & "-" & CY ) RETURN FinYear, "CalWeekNo", WEEKNUM ( [Date], 2 ), "Weekend/Working", IF ( WEEKDAY ( [Date], 2 ) > 5, "Weekend", "Working" ))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
