calculate data based on timelineslicer value in 3 category ThisYear,LastYear,Difference- showon rows

[NehaSha]

Hi All,

Need help for  the below mentioned sample data :data loaded  from 2011 till yesterday date for each day for each location
startdate	salesIn	salesoff	Wrieoff	Ft	Locationid
4/1/2019	10	2	0.5	200	1
4/2/2019	8	0	0.7	201	1
4/3/2019	7	1	1	202	1
4/4/2019	5	0	0.5	203	1
4/5/2019	1	2	0.9	204	1
4/1/2019	9	4	0.8	205	2
4/2/2019	2	3	0.8	206	2
4/3/2019	8	0	0.8	207	2
4/4/2019	4	1	0.8	208	2
4/5/2019	3	1	0.8	209	2
4/1/2018	10	2	0.8	210	1
4/2/2018	15	5	0.8	211	1
4/3/2018	7	1	0.8	212	1
4/4/2018	6	0	0.8	213	1
4/5/2018	3	1	0.8	214	1
4/1/2018	1	2	0.8	215	2
4/2/2018	12	7	0.8	216	2
4/3/2018	3	2	0.8	217	2
4/4/2018	4	0	0.8	218	2
4/5/2018	2	2	0.8	219	2
Report contain 2 slicer Timelineslicer and LocationID Slicer
startdate will always be first of that month
FT column,writeoff column value will be always the value based on Enddate
SalesIn,salesoff, writeoff column will be caluculated sum between  startdate and endate
PercentageCalculatedColumn =sum salein between startdate and enddate / writeoff basedon timelineslicer end date value
EndDate for Timeline Slicer LocationID Slicer 4/4/2019 ALL
Matrix Result Expectation   Actual LY Difference salesIn 53 58 -5 salesoff 11 19 -8 Wrieoff 1.3 3.2 1.9 Percenatge 40.76923 18.125 22.644231 FT 411 431 -20 Please help in how to calculate actual , LY and difference column based on timeline slicer selection and how to define data in category actual ,LY,difference Please help how to represent the data in the form of matrix table which will show data on rows as shown in example Thanks in advance!

[v-cherch-msft]

Hi @NehaSha 

You may unpivot the table in query editor first and then create measures like below:

Actual = 
IF (
    MAX ( Table1[Attribute] ) IN { "salesIn", "salesoff" },
    CALCULATE (
        SUM ( Table1[Value] ),
        FILTER (
            ALL ( Table1[startdate] ),
            Table1[startdate] <= MAX ( Table1[startdate] )
                && Table1[startdate]
                    >= DATE ( YEAR ( MAX ( Table1[startdate] ) ), MONTH ( MAX ( Table1[startdate] ) ), 1 )
        )
    ),
    CALCULATE ( SUM ( Table1[Value] ) )
)

LY = CALCULATE([Actual],SAMEPERIODLASTYEAR(Table1[startdate]))

Difference = [Actual]-[LY]

Percentage Actual = 
DIVIDE (
    CALCULATE ( [Actual], Table1[Attribute] = "salesIn" ),
    CALCULATE ( [Actual], Table1[Attribute] = "wrieoff" )
)

Percentage LY = 
DIVIDE (
    CALCULATE ( [LY], Table1[Attribute] = "salesIn" ),
    CALCULATE ( [Actual], Table1[Attribute] = "wrieoff" )
)

Percentage Difference = [Percentage Actual]-[Percentage LY]

Regards,

[NehaSha]

Hi @v-cherch-msft ,

Really appreciate for your reply!

It worked out as suggested but in requirement there are below mentioned things need to accomplish for user acceptance: 

1. Data Type for each attribute such as $, number with comma, 1 decimal place. Not sure how it can be done. 

2. Need Percentage Column in one table rather than another table, Is there any way out ?

3. Need to represent negative numbers in red.

Matrix Result Expectation
	Actual	LY	Difference
salesIn	$53	$58	($5)
salesoff	$11	$19	($8)	need to show negative numbers in red
Wrieoff	1	3	2	without decimal
Percenatge	40.8%	44.6%	3.9%	Percentage some columns with 1 decimal place some --suppose to show in same part of table rather than different table
FT	411	431	-20	if number is in million  than show with comma

Thank you very much in advance! 

Thanks,

Neha

[v-cherch-msft · Accepted solution]

Hi @NehaSha 

You may use Format Function to get the table.Please refer to this article.Then add a measure and use it in conditional formatting to get red colors.For percentage and comma,you may use format under modeling.There's no better way to show them in same part of table as there's no percentage in attribute column.Attached sample file for your reference.

Actual = 
IF (
    MAX ( Table1[Attribute] ) IN { "salesIn", "salesoff" },
    FORMAT (
        CALCULATE (
            SUM ( Table1[Value] ),
            FILTER (
                ALL ( Table1[startdate] ),
                Table1[startdate] <= MAX ( Table1[startdate] )
                    && Table1[startdate]
                        >= DATE ( YEAR ( MAX ( Table1[startdate] ) ), MONTH ( MAX ( Table1[startdate] ) ), 1 )
            )
        ),
        "$#,##0;($#,##0)"
    ),
    CALCULATE ( SUM ( Table1[Value] ) )
)

Measure = IF([Actual]-[LY]<0,1)

Regards,

[NehaSha]

Hi @v-cherch-msft ,

Thank you very much for reply and give hope to the project!

I need your help with below mentioned scenario which I couldn't cover in the sample example  and have these questions now:

Formating : Looks like in current scenario the calculation based on between startdate and enddate showing always $ sign and the values depend on enddate dont show $ sign if required : 

Sorting: right now sorting based on attribute asc or desc order , is it possible to sort as user requested without any key point. 

Percentage Column: In order to show the project as final presentation the biggest thing is how to get the Percentage column as a part of the same table. In sample it just showing one percentage column but in reality I have total 6 Percentage attribute: Please help to get the Percentage column as a part of same attribute 

In Attached PBIX the result is 

Attribute	Actual	LY	Difference
NetRent	$1,632,000	$1,712,000	$80,000
salesoff	$11	$19	$8
salesIn	$53	$58	$5
Writeoff	1	2	0
Ft	411	431	-20

Expectation :

Attribute	Actual	LY	Difference
salesoff	11	19	8	no need $sign	calculation based on startdate and enddate
salesIn	53	58	5	no need $sign	calculation based on startdate and enddate
NetRent	$1,632,000	$1,712,000	$80,000	need $sign	calculation based on startdate and enddate
Writeoff	1.3	3.2	1.9	one decimalplace	calculation based on enddate
PerOcc	40.8%	44.6%	-3.8%	need Percentage column with 1 decimal as part of table	calculation based on startdate and enddate
Ft	$411	$431	($20)	nodecimal place but $sign	calculation based on  enddate
sorting attribute in user requested order there is no asc or desc criteria

Thanks,

Neha

[NehaSha]

Hi @v-cherch-msft ,

I would like to follow up , if you have time to look into the below  mentioned scenario ? It will be really helpful!

Is it really possible to achieve?

Formating : Looks like in current scenario the calculation based on between startdate and enddate showing always $ sign and the values depend on enddate dont show $ sign if required :

              But is it possible to define each attribute format, I have added new column netrent (Copy of FT column added 3zeros in end for each value) which also calculated based on startdate and enddate shows $ sign but salesoff and salesIn dont need $ sign. similarly writeoff need one decimal place and FT need $ sign without decimal place. 

Sorting: right now sorting based on attribute asc or desc order , is it possible to sort as user requested without any key point. neither sorting based on Attribute alphabet nor on values , its just user requested in the below mentioned order.

Percentage Column: In order to show the project as final presentation the biggest thing is how to get the Percentage column as a part of the same table. In sample it just showing one percentage column but in reality I have total 6 Percentage attribute. Please help to get the Percentage column as a part of same attribute 

In Attached PBIX the result is 

Attribute	Actual	LY	Difference
NetRent	$1,632,000	$1,712,000	$80,000
salesoff	$11	$19	$8
salesIn	$53	$58	$5
Writeoff	1	2	0
Ft	411	431	-20

Expectation :

Attribute	Actual	LY	Difference
salesoff	11	19	8	no need $sign	calculation based on startdate and enddate
salesIn	53	58	5	no need $sign	calculation based on startdate and enddate
NetRent	$1,632,000	$1,712,000	$80,000	need $sign	calculation based on startdate and enddate
Writeoff	1.3	3.2	1.9	one decimalplace	calculation based on enddate
PerOcc	40.8%	44.6%	-3.8%	need Percentage column with 1 decimal as part of table	calculation based on startdate and enddate
Ft	$411	$431	($20)	nodecimal place but $sign	calculation based on  enddate
sorting attribute in user requested order there is no asc or desc criteria

Thanks,

Neha

[NehaSha]

I tried but couldnt attach the PBIX. But the NetRent Column is the Duplicate column for FT and rename to 'NetRent'  and added 3 zeros in the end for all the values, added in change type 

= Table.DuplicateColumn(Source, "Ft", "Ft - Copy")

= Table.ReplaceValue(#"Duplicated Column","200","200000",Replacer.ReplaceText,{"Ft - Copy"})--repeat for all values 

= Table.RenameColumns(#"Replaced Value19",{{"Ft - Copy", "NetRent"}})

= Table.TransformColumnTypes(#"Renamed Columns",{{"startdate", type date}, {"salesIn", Int64.Type}, {"salesoff", Int64.Type}, {"Wrieoff", type number}, {"Ft", Int64.Type}, {"Locationid", Int64.Type},{"NetRent",Int64.Type}})

Actual Measure change:

Actual =

IF (

MAX ( Table1[Attribute] ) IN { "salesIn", "salesoff","NetRent" },

FORMAT (

CALCULATE (

SUM ( Table1[Value] ),

FILTER (

ALL ( Table1[startdate] ),

Table1[startdate] <= MAX ( Table1[startdate] )

&& Table1[startdate]

>= DATE ( YEAR ( MAX ( Table1[startdate] ) ), MONTH ( MAX ( Table1[startdate] ) ), 1 )

)

),

"$#,##0;($#,##0)"

),

CALCULATE ( SUM ( Table1[Value] ) )

)

 Measure : 

Difference =

IF (

MAX ( Table1[Attribute] ) IN { "salesIn", "salesoff" ,"NetRent" },

FORMAT ( [Actual] - [LY], "$#,##0;($#,##0)" ),

[Actual] - [LY]

)

Please help the below reply scenarios to resolve!

Thanks,

Neha