Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have quick question.
Is it possible to create measure which will sum cumulatevily values over 2 years?
I mean it must be something like YTD, but for 2 years, not only one year.
My current measure looks like this:
My problem arise when 2020 start, so my cumulative Revenues are approximately 0, but I need to sum them with previous year cumulatevily.
Solved! Go to Solution.
Hi @pawelj795 ,
First, one step in the above formula was complicated by me. I have modified it. Please check:
YTD Revenues 5 =
VAR LastYearFirstDate =
IF (
SELECTEDVALUE ( 'Calendar'[Year] ) = BLANK (),
DATE ( YEAR ( LASTDATE ( Invent_Trans[Date] ) ) - 1, 1, 1 ),
DATE ( SELECTEDVALUE ( 'Calendar'[Year] ) - 1, 1, 1 ) -------------changed
)
VAR CurrentDate =
IF (
SELECTEDVALUE ( 'Calendar'[Year] ) = BLANK (),
MAX ( DimDates[Date] ),
CALCULATE (
MAX ( DimDates[Date] ),
FILTER ( DimDates, DimDates[Year] = SELECTEDVALUE ( 'Calendar'[Year] ) )
)
)
RETURN
CALCULATE (
SUM ( Invent_Trans[Inventory Value] ),
FILTER (
ALLSELECTED ( DimDates ),
DimDates[Date] >= LastYearFirstDate
&& DimDates[Date] <= CurrentDate
)
)
Then, you can create your YearWeek column like so:
YearWeek =
CONCATENATE (
Invent_Trans[Year],
CONCATENATE ( " ", FORMAT ( Invent_Trans[WeekNum], "00" ) )
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
i have a very similar problem.
I have a dimensional table, containing the number of Stores, I want to have the cumulative sum per year of the number of active stores.
See example:
therefore the number of active stores in 2012 is given by the sum of the stores created in 2011 and 2012.
The number of stores active in 2013 will be given by the sum of the stores active in 2011 and 2012.
Can you help me to solve this problem?
Thank you
@Icey
@amitchandak
I was just trying this solution, but it doesn't seem working.
Why you using EndOfMonth function? What is the purpose?
Hi @pawelj795 ,
I create a simple example. What about this?
YTD Revenues 3 =
VAR LastYearFirstDate =
DATE ( YEAR ( LASTDATE ( Invent_Trans[Date] ) ) - 1, 1, 1 )
VAR CurrentDate =
MAX ( DimDates[Date] )
RETURN
CALCULATE (
SUM ( Invent_Trans[Inventory Value] ),
FILTER (
ALLSELECTED ( DimDates ),
DimDates[Date] >= LastYearFirstDate
&& DimDates[Date] <= CurrentDate
)
)
For details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Icey
It's working!
But I need small adjustment in my report.
I have slicers for years (2019/2020 or both) in whole page, so when I switch for only 2020, values are incorrect (which is valid)
Do you have any idea how to smartly create slicers that measure would work for 2020 and viewers of my report can also turn on 2020 for rest of report?
Hi @pawelj795 ,
In my scenario, it works well. Maybe I understand something wrong, please let me know. And if you don't mind, please share me a dummy PBIX file, without real data or sensitive information.
Best Regards,
Icey
@Icey
Maybe, I explain it incorrectly.
I mean that when I select only 2020 in slicer, I want to have cumulative value from 2019 too.
To sum up, I want to choose only 2020 in slicer, but I want to have cumulative value from 2019 and 2020 too.
In your case, would be 119 for 2-1-2020.
Hi @pawelj795 ,
For "single select", ty this:
1. Create another Calendar table without relationships among others.
Calendar = ADDCOLUMNS ( CALENDARAUTO (), "Year", YEAR ( [Date] ) )
2. Create a measure.
YTD Revenues 4 =
VAR LastYearFirstDate =
IF (
SELECTEDVALUE ( 'Calendar'[Year] ) = BLANK (),
DATE ( YEAR ( LASTDATE ( Invent_Trans[Date] ) ) - 1, 1, 1 ),
DATE ( YEAR (
CALCULATE (
LASTDATE ( Invent_Trans[Date] ),
FILTER ( DimDates, DimDates[Year] = SELECTEDVALUE ( 'Calendar'[Year] ) )
)
) - 1, 1, 1 )
)
VAR CurrentDate =
IF (
SELECTEDVALUE ( 'Calendar'[Year] ) = BLANK (),
MAX ( DimDates[Date] ),
CALCULATE (
MAX ( DimDates[Date] ),
FILTER ( DimDates, DimDates[Year] = SELECTEDVALUE ( 'Calendar'[Year] ) )
)
)
RETURN
CALCULATE (
SUM ( Invent_Trans[Inventory Value] ),
FILTER (
ALLSELECTED ( DimDates ),
DimDates[Date] >= LastYearFirstDate
&& DimDates[Date] <= CurrentDate
)
)
3. Create a Year slicer from Calendar table.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Icey
It's finally seems working 🙂
One question though, in my main date table I add column with combined year and week number.
y
How to sort them correctly?
Hi @pawelj795 ,
First, one step in the above formula was complicated by me. I have modified it. Please check:
YTD Revenues 5 =
VAR LastYearFirstDate =
IF (
SELECTEDVALUE ( 'Calendar'[Year] ) = BLANK (),
DATE ( YEAR ( LASTDATE ( Invent_Trans[Date] ) ) - 1, 1, 1 ),
DATE ( SELECTEDVALUE ( 'Calendar'[Year] ) - 1, 1, 1 ) -------------changed
)
VAR CurrentDate =
IF (
SELECTEDVALUE ( 'Calendar'[Year] ) = BLANK (),
MAX ( DimDates[Date] ),
CALCULATE (
MAX ( DimDates[Date] ),
FILTER ( DimDates, DimDates[Year] = SELECTEDVALUE ( 'Calendar'[Year] ) )
)
)
RETURN
CALCULATE (
SUM ( Invent_Trans[Inventory Value] ),
FILTER (
ALLSELECTED ( DimDates ),
DimDates[Date] >= LastYearFirstDate
&& DimDates[Date] <= CurrentDate
)
)
Then, you can create your YearWeek column like so:
YearWeek =
CONCATENATE (
Invent_Trans[Year],
CONCATENATE ( " ", FORMAT ( Invent_Trans[WeekNum], "00" ) )
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HI @Icey
I need to dig up my topic.
Currently, I'm updating this report and I need to add slicer with quarters and week numbers, but it require some modification to your measure.
In current conditions, I can only add slicer with year from table "Calendar".
Can you help me?
That work's perfectly!
Thanks
Try Like
Sales 2 Years =
IF(
NOT ISEMPTY( Sales ),
CALCULATE(
[Sales],
DATESINPERIOD('DimDates'[Date],ENDOFMONTH(Sales[Sales Date]),-2,YEAR))
)
)
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |