Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
in my table, I got a column id column date and column net profit
if I make this measure
Profit prev year2 = CALCULATE (SUM(ViewProjectProfit[NetProfit]), SAMEPERIODLASTYEAR( ViewProjectProfit[dtAccounting] ) )
but this measure give just the total that is correct but doesn't make the monthly split as for the other column
Solved! Go to Solution.
hi, @snifer
SAMEPERIODLASTYEAR Function is Time Intelligence Functions it needs a date table
and for you formula, [dtAccounting] is from basic data table so it will leads this issue.
You could do these as below to solve it.
Step1:
Add a date table and create the relationship with basic data table
Step2:
Create a new Profit prev year2 by this formula
new Profit prev year = CALCULATE (SUM(ViewProjectProfit[NetProfit]), SAMEPERIODLASTYEAR('Date'[Date]))
Step3:
Drag the date field from date table instead of ViewProjectProfit[dtAccounting] into visual
here is demo pbix, please try it.
Best Regards,
Lin
hi, @snifer
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Best Regards,
Lin
hi, @snifer
SAMEPERIODLASTYEAR Function is Time Intelligence Functions it needs a date table
and for you formula, [dtAccounting] is from basic data table so it will leads this issue.
You could do these as below to solve it.
Step1:
Add a date table and create the relationship with basic data table
Step2:
Create a new Profit prev year2 by this formula
new Profit prev year = CALCULATE (SUM(ViewProjectProfit[NetProfit]), SAMEPERIODLASTYEAR('Date'[Date]))
Step3:
Drag the date field from date table instead of ViewProjectProfit[dtAccounting] into visual
here is demo pbix, please try it.
Best Regards,
Lin
thank you now working fine, one last question how I can display the % simbol near the value on the %variazione colum
HI, @snifer
After my test, you could achieve it like this
Add "%"& in your formula
for example
Measure = "%"&IF(CALCULATE(MAX(Project[Report As At ]),ALLSELECTED(Project))=MAX(Project[Report As At ]),1,2)
by the way, if you do this, the format of this measure will be Text.
Best Regards,
Lin
adding the "%"& to the formula change my column in text that is not good for me
hi, @snifer
The format of "%+numerical value" can't be the numeric type, it only can be text type.
You can format it into "numerical value+%" or format it into "%+numerical value" as a final result。
If you continue to use it for later calculations, you could copy this measure, One is %variazione for this visual and another is variazione for later calculations.
Best Regards,
Lin
i try to add +% but i got erro of offeset
hi, @snifer
I have written three different formulas of measure for you refer to.
and the keep numerical Measure is the numeric type and can be set by: select the measure->click modeling->formating
and other two measure are text type
%Measure = "%"&IF(CALCULATE(MAX(Project[Report As At ]),ALLSELECTED(Project))=MAX(Project[Report As At ]),1,2) Measure% = IF(CALCULATE(MAX(Project[Report As At ]),ALLSELECTED(Project))=MAX(Project[Report As At ]),1,2)&"%"
here is pbix, please try it.
Best Regards,
Lin
Year and Month Columns, are comming from what table?
For SAMEPERIODLASTYEAR to work properly, usualy it needs a Date Table. You can try creating a Date dimension table and create a relationship between dtAccounting and Date. Afterwards, you can try SAMEPERIODLASTYEAR(Date[Date]).
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |