Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
i have the following data:
id | yearMonth | sales |
1 | 2023-03 | 81€ |
2 | 2023-04 | 90€ |
3 | 2022-08 | 32€ |
... | ... | ... |
Now I want to create some measurements in specific "date ranges". I have to use field yearMonth to create that measurements. I need the following:
How can I (first) define the date ranges? Can someone help me please?
Thanks!
Hello Faithu - so from what I see here - you do not have an actual Data field - such as 04/17/2023? - you only have a column YearMonth? Is this correct?
If that is the case you will have to employee certain date functions and possibly use Set Analysis to extrapolate the values.
For example this is current month:
Sum({<[Order Date.autoCalendar.YearMonth]={"$(=Year(Today()) & '-' & Month(Today()))"}>}[Sales])
This is last month:
Sum({<Indicator={'Y'},[Order Date.autoCalendar.YearMonth]={"$(=Year(Today()) & '-' & Month(MonthStart(Today(),-1)))"}>}[PartSales])
(notice the -1 in the Today expression)
This is an expressions which Sums the measure (field) Sales - where the YearMonth dimension (field) equals today's month and today's year - they are formatted and concatenated basically to match the value in the data. If you have real dates in your data - it is much easier to perform time period analysis as we have tools and an engine that automatically do stuff like that.
in case you are not familiar with set analysis - here are some resources that can help:
Let me know if this helps.