DAX for YTD, LY YTD ,+/-,YTD LY%

[neelofarshama]

Hi,

 I have a scenario where I have date and Total, I am supposed to calculate LY Total, LY +/-, LY%, YTD, YTD LY,+/- YTD, YTD LY% for every filter (date) selection in power BI.

Below is the excel attached with sample data.

Any help is highly appreciated.

[v-easonf-msft]

Hi , @neelofarshama 

I am a bit confused about your sample data.
What is the "dimension" column used for?

Why the  the sample data contains two records with the same year_month(2020 january,2020 February,2020 March).

If possible, please show the original  data including last year's.

Best Regards,
Community Support Team _ Eason

[amaleranda]

Hi @neelofarshama ,

I manged to get the list of measure you want to Please see below output. I created a data tabel and linked to data on one to many relationship with date in the fact tabel ( your tabel with totals).

for more info contact info@nykensanalytics.com.au

Below are the respective measures.

====CURRENT TOTAL======

Current Total =

VAR Result =

SUM ( Sheet1[Total] )

RETURN

Result

===TOTAL LAST YEAR====

Total LY =

VAR Result =

CALCULATE (

[Current Total],

SAMEPERIODLASTYEAR ( 'Date'[Date] )

)

RETURN

Result

====VARIANCE LAST YEAR===

Variance LY =

VAR Result =

[Current Total] - [Total LY]

RETURN

Result

====% VARIANCE LAST YEAR==

% Variance LY =

VAR Result =

DIVIDE (

[Variance LY],

[Current Total]

)

RETURN

Result

==== TOTAL YEAR TO DATE==

Total YTD =

VAR Result =

TOTALYTD (

[Current Total],

'Date'[Date]

)

RETURN

Result

=====TOTAL YEAR TO DATE LAST YEAR==

Total YTD LY =

VAR Result =

CALCULATE([Total YTD],

SAMEPERIODLASTYEAR('Date'[Date]

)

)

RETURN

Result

======VARIANCE YTD LAST YEAR==

Variance YTD LY =

VAR Result =

[Total YTD] - [Total YTD LY]

RETURN

Result

====% VARIANCE YEAR TO DATE LAST YEAR===

% Variance YTD LY =

VAR Result =

DIVIDE (

[Variance YTD LY],

[Total YTD]

)

RETURN

Result

[Ashish_Mathur]

Hi,

Share the link from where i can download your Excel file which has the raw input.  Is your FY from January to Decemner?

[neelofarshama]

Hi,

Thank you for the reply please share your mail id so that i can send you the file

[v-easonf-msft]

Hi , @neelofarshama 

For the protection of your data security, it is recommended to upload a simple demo to onedrive for business . Remember to not post any sensitive data while doing this. 

Best Regards,
Community Support Team _ Eason