Forum Discussion

mpmsltd's avatar
mpmsltd
Icon for Helper I rankHelper I
6 years ago
Solved

Look back 365 days calculation

Hi everybody,


I have a table with dates, dollar value and category of each transaction. I want to see my margin for the past 12 months - but each data point on my chart (example below) should calculate back 12 months.

For example if I look at my % for September 2019 it should consider the previous 12 months on the calculation.

The table looks pretty much like the below example. I also would like to sum for the past 12 months only for the "Sales" category. Can somebody please help me out with this? Thank you!!

 

date total category
1/1/2019 $    50.00sale
12/8/2018 $  100.00taxes
3/5/2019 $  150.00freight
1/1/2019 $    50.00sale
12/8/2018 $  100.00taxes
3/5/2019 $  150.00freight
1/1/2019 $    50.00sale
12/8/2018 $  100.00taxes
12/9/2018 $  150.00freight

 

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    6 years ago

    mpmsltd 

     

    Add a calendar table and build relationship, then add measure below.

    Measure =
CALCULATE (
    SUM ( 'Table'[total] ),
    DATESINPERIOD ( 'Calendar'[date], MAX ( 'Calendar'[Date] ), -12, MONTH ),
    'Table'[category] = "sale"
)

     

2 Replies