Forum Discussion
Create Data Range filters using one date column in PowerBI
Hi, I have Table A with Date and Session columns. Using Date column i have to create 4 different Date range filters like Start Date, End Date, Compare Start Date, Compare End Date. Compare Start D...
Thanks Jing.
I have sorted out Range filter now. I have an other query
Need to calculate Revenue based on the dates (Screenshot for example)
Start and End Date range
Compare Start and end date Range
Session % = Dates bw start and end of revenue calculation/ dates bw compare start and end Date Range of revenue calculation
DAx Used :
Revenue4num =
divide(CALCULATE(
SUM(st_sessions_new[totals_total_transactions_revenue]),
FILTER(
st_sessions_new,
st_sessions_new[date] >= SELECTEDVALUE(DimDate[StartDate]) &&
st_sessions_new[date] <= MAX(DimDate[StartDate])
)
),1000000)
Revenue4den =
divide(CALCULATE(
SUM(st_sessions_new[totals_total_transactions_revenue]),
FILTER(
st_sessions_new,
st_sessions_new[date] >= SELECTEDVALUE(DimDate[CompareStartDate]) &&
st_sessions_new[date] <= MAX(DimDate[CompareStartDate])
)
),1000000)
REvenud_diff1%=
DIVIDE(Revenue4num, Revenue4den, 0) - 1
Thanks in advance.
Cheers,
SaiPerumal
Hi Anonymous
You need two date tables, one for the first date slicer and the other for the comparison range slicer.
Relationships:
As the relationship exists between DimDate table and the fact table, the revenue measure could be:
Revenue4num = SUM('Table'[Revenue])
For the other measure, it uses the CompareDate table's date column. The CompareDate table should be disconnected with other tables. So the measure could be:
Revenue4den =
VAR vStartDate = MIN(CompareDate[Date])
VAR vEndDate = MAX(CompareDate[Date])
RETURN
CALCULATE(SUM('Table'[Revenue]),ALLEXCEPT('Table','Table'[Country]),'Table'[Date]>=vStartDate, 'Table'[Date]<=vEndDate)
Result:
I have attached the demo pbix at bottom, hope it would be helpful.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!