Help getting the total amount per week, for income related to valid tickets for transport service

[martingrondahl]

Hi!

I work at a transport service company that sell tickets valid for different time periods (1 day, 7 day, 30 day etc.). I've created a measure in Power BI that calculates the income generated per day from 1 day tickets, as well as the income generated from earlier sold tickets that are still active (evenly distributed out across the validity period), so that I can get a picture of total daily value of tickets active on a given date. I used an example from a video made by How to Power BI on Youtube that looks at active employees to create the measure. 

The measure looks like this: 

Income per day CY =

VAR Currentdate = max('0 / Datetable'[Date])

VAR Income=

CALCULATE(sum('8 / DMI_Analyse v_F_TicketSales'[Income per day validity period),

all('0 / Datetable'),

'0 / Datetable'[Date] <= Currentdate,

'8 / DMI_Analyse v_F_TicketSales'[Valid to date]>=Currentdate)

RETURN

Income

The measure works as intended when a look at a given date, but I can't find a way to calculate the sum for a given time period (week, month etc.). I've tried datesinperiod, dateadd and datesbetween without any success. Is there someone out there that can help? 

Please let me know if you need more details. 

Thank you,

Martin

[v-zhengdxu-msft]

Hi @martingrondahl 

Could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

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

[tlauronen]

What is the logic of your "8 / DMI_Analyse v_F_TicketSales" table and what does each row contain? I have a feeling you might be missing logic to multiply the daily value of a ticket by the number of days in your desired date range.

I might also consider building a separate table with aggregated sales values by date for this use case. You could also add the count of each ticket type active as separate columns. This would simplify building your business analysis.

[martingrondahl]

Hi and thanks for your reply. "8 / DMI_Analyse v_F_TicketSales" includes: 

Sales date

Ticket type

Valid to date

Number of tickets 

Net income

Income per day validity period

Each row contains aggregated sales by date, by ticket type.