Forum Discussion
Help in DAX formula - weekly data comparison
Hi All,
Good day! I just need some help on my DAX formula
I'm comparing weekly data. I'm comparing week 1 data of this year to the week 1 data of last year. I have already my formula and the result is correct. But there is no total at the bottom of the table visual. See below.
No sub total for the measure Print Cost Last Year
Here is the formula for the Print Cost Last Year
PRINT COST LAST YEAR =
var currentyear = SELECTEDVALUE(Dates[Year])
var lastyear = currentyear-1
var currentweekofyear = SELECTEDVALUE(Dates[Week of Year])
var computedPrintCostLastYr = CALCULATE([TOTAL PRINT COST],Dates[Year]=lastyear && Dates[Week of Year]=currentweekofyear)
return
if (computedPrintCostLastYr=BLANK(),0,computedPrintCostLastYr)
If you need the powerbi file and the excel source file I can provide it since every data is just a sample.
Thank you very much.
4 Replies
- amitchandak
Super User
jc_pbi , return this and try
Sumx(addcolumns(summarize(Dates, Dates[Year], Dates[Week]), "_1", CALCULATE([TOTAL PRINT COST],Dates[Year]=lastyear && Dates[Week of Year]=currentweekofyear)),[_1])
Hi amitchandak
Still with no sub total.
Here is the whole formula.
PRINTCOSTLASTYEAR =var currentyear = SELECTEDVALUE(Dates[Year])var lastyear = currentyear-1var currentweekofyear = SELECTEDVALUE(Dates[Week of Year])var computedPrintCostLastYr = CALCULATE([TOTAL PRINT COST],Dates[Year]=lastyear && Dates[Week of Year]=currentweekofyear)returnSumx(addcolumns(summarize(Dates, Dates[Year], Dates[Week of Year]), "_1", CALCULATE([TOTAL PRINT COST],Dates[Year]=lastyear && Dates[Week of Year]=currentweekofyear)),[_1])or screen shot of the formula- amitchandak
jc_pbi ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
