cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
NehaSha Regular Visitor
Regular Visitor

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

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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)

1.png

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Community Support Team
Community Support Team

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

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
NehaSha Regular Visitor
Regular Visitor

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

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  
 ActualLYDifference 
salesIn$53$58($5) 
salesoff$11$19($8)need to show negative numbers in red
Wrieoff132without decimal
Percenatge40.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
FT411431-20if number is in million  than show with comma 

 

Thank you very much in advance! 

 

Thanks,

Neha

 

Community Support Team
Community Support Team

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

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)

1.png

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
NehaSha Regular Visitor
Regular Visitor

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

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 ActualLYDifference
NetRent$1,632,000$1,712,000$80,000
salesoff$11$19$8
salesIn$53$58$5
Writeoff120
Ft411431-20

 

Expectation :

Attribute ActualLYDifference  
salesoff11198no need $signcalculation based on startdate and enddate
salesIn53585no need $signcalculation based on startdate and enddate
NetRent$1,632,000$1,712,000$80,000need $sign calculation based on startdate and enddate
Writeoff1.33.21.9one decimalplacecalculation based on enddate
PerOcc40.8%44.6%-3.8%need Percentage column with 1 decimal as part of tablecalculation based on startdate and enddate
Ft$411$431($20)nodecimal place but $signcalculation based on  enddate
sorting attribute in user requested order there is no asc or desc criteria  

 

 

Thanks,

Neha

NehaSha Regular Visitor
Regular Visitor

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

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
NehaSha Regular Visitor
Regular Visitor

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

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 ActualLYDifference
NetRent$1,632,000$1,712,000$80,000
salesoff$11$19$8
salesIn$53$58$5
Writeoff120
Ft411431-20

 

Expectation :

Attribute ActualLYDifference  
salesoff11198no need $signcalculation based on startdate and enddate
salesIn53585no need $signcalculation based on startdate and enddate
NetRent$1,632,000$1,712,000$80,000need $sign calculation based on startdate and enddate
Writeoff1.33.21.9one decimalplacecalculation based on enddate
PerOcc40.8%44.6%-3.8%need Percentage column with 1 decimal as part of tablecalculation based on startdate and enddate
Ft$411$431($20)nodecimal place but $signcalculation based on  enddate
sorting attribute in user requested order there is no asc or desc criteria  

 

Thanks,

Neha