Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to create columns for YTD, WTD and transactions that happened yesterday for mulitple different measures in one matrix. How can I do this?
Solved! Go to Solution.
Hi @JJL1990 ,
Based on my research, we can’t put the measure as column in Metrix. If you’d like to calculate YTD and WTD for measures, you could use table rather than Metrix. Also the transaction happened yesterday will be recorded in measure as well using table.
Below steps and attached pbix are for your reference: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/ETiPpoUalpxPhb0fnmsubJ...
Leadsoffered = MAX(Table1[Volume])*1 LeadsReferred = IF(MAX([Volume])=BLANK(),BLANK(),MAX([Volume])+2) Referralrate = LeadsReferred/ Leadsoffered
2. Create a new calendar table with dates from 2018/12/1- 2019/6/30, and manage the relationship between them, aims to use time intelligence function:
3. Created 2 new columns to show week number:
wn = WEEKNUM([Date1],1) yr = YEAR([Date1])
4. You can use the codes following to generate YTD for volume and YTD for Leadoffered(LO YTD):
YTD volume = TOTALYTD(SUM(Table1[Volume]),Table1[Date]) LO YTD = IF(SUM(Table1[Volume])=BLANK(),BLANK(),CALCULATE(SUMX('Table1',[Leadsoffered]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])),VALUES('Table1'[yr]))) WTD volume = CALCULATE(SUM(Table1[Volume]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])),VALUES(Table1[wn])) LO WTD= IF(SUM(Table1[Volume]) = BLANK(),BLANK(),CALCULATE(SUMX(Table1,[Leadsoffered]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])),VALUES(Table1[wn])))
LF YTD, RR YTD….by that analogy
Best regards,
Dina Ye
Hi @JJL1990 ,
Based on my research, we can’t put the measure as column in Metrix. If you’d like to calculate YTD and WTD for measures, you could use table rather than Metrix. Also the transaction happened yesterday will be recorded in measure as well using table.
Below steps and attached pbix are for your reference: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/ETiPpoUalpxPhb0fnmsubJ...
Leadsoffered = MAX(Table1[Volume])*1 LeadsReferred = IF(MAX([Volume])=BLANK(),BLANK(),MAX([Volume])+2) Referralrate = LeadsReferred/ Leadsoffered
2. Create a new calendar table with dates from 2018/12/1- 2019/6/30, and manage the relationship between them, aims to use time intelligence function:
3. Created 2 new columns to show week number:
wn = WEEKNUM([Date1],1) yr = YEAR([Date1])
4. You can use the codes following to generate YTD for volume and YTD for Leadoffered(LO YTD):
YTD volume = TOTALYTD(SUM(Table1[Volume]),Table1[Date]) LO YTD = IF(SUM(Table1[Volume])=BLANK(),BLANK(),CALCULATE(SUMX('Table1',[Leadsoffered]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])),VALUES('Table1'[yr]))) WTD volume = CALCULATE(SUM(Table1[Volume]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])),VALUES(Table1[wn])) LO WTD= IF(SUM(Table1[Volume]) = BLANK(),BLANK(),CALCULATE(SUMX(Table1,[Leadsoffered]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])),VALUES(Table1[wn])))
LF YTD, RR YTD….by that analogy
Best regards,
Dina Ye
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
94 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |