The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi,
I want to calculate YTD and use this calculateion for different years. The YTD formula works fine for 2019 and 2021, but for 2020 it is a problem due to 53 weeks. It summuraize, but it will not show a subtotal thus it will not aggregate.
I tried another formula in order to take into account 53 weeks:
Solved! Go to Solution.
Hi @PowerMarra ,
I created some data:
Hovedmeasures:
Calendar:
The relationship between the two tables is one-to-one and Both.
Here are the steps you can follow:
1. Create measure.
YTD Value TEST =
CALCULATE(SUM('Hovedmeasures'[rand]),DATESYTD('Calendar'[Date]))
2. Result:
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @PowerMarra ,
I created some data:
Hovedmeasures:
Calendar:
The relationship between the two tables is one-to-one and Both.
Here are the steps you can follow:
1. Create measure.
YTD Value TEST =
CALCULATE(SUM('Hovedmeasures'[rand]),DATESYTD('Calendar'[Date]))
2. Result:
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@PowerMarra , I think a measure like this should work , assuming [Value Sales] is a measure else use an aggregation like sum([Value Sales])
CALCULATE([Value Sales],
fILTER(ALL('Calendar'),
'Calendar'[Iso Year] = max('Calendar'[Iso Year]) && 'Calendar'[Week] <= max('Calendar'[Week] ) )
)
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
13 | |
8 | |
8 |