Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I have excel file. I need 2 Measure.
1) Dynamic previous year and all Previous year growth sales on filter year.
2) I need to add january month data with february month then february month to march month till Novermber month to December month. using measure or column.
*Note: below image has 2 red boxes
1) Year growth by month (Which I want Dynamic Measure with year filter)
2) january month data with february month then february month to march month till Novermber month to December month is calculationg from above table in same image so I want to add all month one by one to each other. (Using Measure or column.) I don't want use second table. I want to make 2nd table using 1st table using measure.
I am sharing a excel file :
https://drive.google.com/open?id=1CZmN47ckRu99WwVrDWh7Ul_llserMte-
I am sharing .pbix file:
https://drive.google.com/open?id=1ehNf6FRjfE8RjYaBhm9huMc7o9A75BPn
Solved! Go to Solution.
Hi,
According to your description, please take following steps:
1)Copy original [Approvals by month] as a new table for unpivot.
2)Unpivot it and add an index column.
3)Create a year slicer table:
Year SLicer = DISTINCT(SELECTCOLUMNS('Table',"Year",'Table'[Approvals by month]))
4)Create a Month number column in above copy table:
_Month = RIGHT('Table'[Month],LEN('Table'[Month])-LEN(LEFT('Table'[Month],FIND("_",'Table'[Month],1,0))))
5)Create growth by year by select year measure:
Growth =
VAR LastYearValues =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Approvals by month]
= MAX ( 'Table'[Approvals by month] ) - 1
&& 'Table'[_Month] = MAX ( 'Table'[_Month] )
)
)
VAR check =
IF (
MAX ( 'Table'[Approvals by month] ) = SELECTEDVALUE ( 'Year SLicer'[Year] ),
1,
0
)
RETURN
IF (
LastYearValues <> BLANK ()
&& check = 1,
( MAX ( 'Table'[Value] ) - LastYearValues ) / LastYearValues
)
6)Create a cumulative values measure:
Cumulative =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Approvals by month] IN FILTERS ( 'Table'[Approvals by month] )
&& 'Table'[Index] <= MAX ( 'Table'[Index] )
)
)
7)Create a cumulative growth by month by select year:
Cumulative Growth =
VAR LastYearValues =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Approvals by month]
= MAX ( 'Table'[Approvals by month] ) - 1
&& 'Table'[_Month] <= MAX ( 'Table'[_Month] )
)
)
VAR check =
IF (
MAX ( 'Table'[Approvals by month] ) = SELECTEDVALUE ( 'Year SLicer'[Year] ),
1,
0
)
RETURN
IF (
MAX ( 'Table'[Approvals by month] ) = SELECTEDVALUE ( 'Year SLicer'[Year] )
&& check = 1,
( [Cumulative] - LastYearValues ) / LastYearValues
)
8)When select one year in slicer, the result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
Hi,
According to your description, please take following steps:
1)Copy original [Approvals by month] as a new table for unpivot.
2)Unpivot it and add an index column.
3)Create a year slicer table:
Year SLicer = DISTINCT(SELECTCOLUMNS('Table',"Year",'Table'[Approvals by month]))
4)Create a Month number column in above copy table:
_Month = RIGHT('Table'[Month],LEN('Table'[Month])-LEN(LEFT('Table'[Month],FIND("_",'Table'[Month],1,0))))
5)Create growth by year by select year measure:
Growth =
VAR LastYearValues =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Approvals by month]
= MAX ( 'Table'[Approvals by month] ) - 1
&& 'Table'[_Month] = MAX ( 'Table'[_Month] )
)
)
VAR check =
IF (
MAX ( 'Table'[Approvals by month] ) = SELECTEDVALUE ( 'Year SLicer'[Year] ),
1,
0
)
RETURN
IF (
LastYearValues <> BLANK ()
&& check = 1,
( MAX ( 'Table'[Value] ) - LastYearValues ) / LastYearValues
)
6)Create a cumulative values measure:
Cumulative =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Approvals by month] IN FILTERS ( 'Table'[Approvals by month] )
&& 'Table'[Index] <= MAX ( 'Table'[Index] )
)
)
7)Create a cumulative growth by month by select year:
Cumulative Growth =
VAR LastYearValues =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Approvals by month]
= MAX ( 'Table'[Approvals by month] ) - 1
&& 'Table'[_Month] <= MAX ( 'Table'[_Month] )
)
)
VAR check =
IF (
MAX ( 'Table'[Approvals by month] ) = SELECTEDVALUE ( 'Year SLicer'[Year] ),
1,
0
)
RETURN
IF (
MAX ( 'Table'[Approvals by month] ) = SELECTEDVALUE ( 'Year SLicer'[Year] )
&& check = 1,
( [Cumulative] - LastYearValues ) / LastYearValues
)
8)When select one year in slicer, the result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
This data needs some work. First of Unpivot the data and convert month 1 into the correct month. Then join with a date table and you can do analysis
https://radacad.com/pivot-and-unpivot-with-power-bi
For YTD and LYTD you can use measure like
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
124 | |
79 | |
50 | |
38 | |
38 |
User | Count |
---|---|
196 | |
80 | |
70 | |
51 | |
42 |