How to write dax formula which should calculate on selection of a date on slicer?

[Anmolgan]

How can write a DAX measure so that I select my date from the slicer and last year from the slicer, and the DAX function should calculate total sales from last year to that date?

[v-lili6-msft]

HI @Anmolgan 

Just try use the logic of this formula:

Result = 
CALCULATE (
    SUM ( 'Monthly Collection Report PBI'[Debit credit value] ),
    DATESBETWEEN (
        Data[Data],
        SAMEPERIODLASTYEAR( Data[Data] ),
        LASTDATE ( Data[Data] )
    )
)

Regards,

Lin

[Anmolgan]

Now I have 2 dates one is the date where I define my parameter and 2nd date table is where I match this date with all the other dates, so i am confused how can I edit the DAX to actual match the parameter date passed with all the other dates that are in other table?

[v-lili6-msft]

hi @Anmolgan 

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[Anmolgan]

Hi @v-lili6-msft  am attaching the one drive link for sample data, please let me know what can be done here, the Data[Data] is my by default parameter, I want to pass in the parameter where Result should caluclate from that period -1 year, that will be my amount.

https://ifitech-my.sharepoint.com/:u:/g/personal/anmol_ganju_ifi_tech/ESpM9SH6tThNlTV3nQSgGksB3JG_cI...

[v-lili6-msft]

hi @Anmolgan 

After my test on your sample pbix file, I found that the column [Base Date.Base Date Level 01] in Monthly Collection Report PBI is not a date column but Data[data] is a date column, so the relationship between them is wrong.

You should adjust it as below:

Step1:

Add a column that format [data] column is 'dd.mm.yyyy'

NEWFORMAT DATA = FORMAT(Data[Data],"dd.mm.yyyy")

Step2:

Then create the relationship between Monthly Collection Report PBI table and data table by this column

Step3:

Now adjust the formula as below:

Opening Bal = CALCULATE(SUM('Monthly Collection Report PBI'[Debit credit value]), DATESBETWEEN(Data[Data], SAMEPERIODLASTYEAR(Data[Data]), LASTDATE(Data[Data])))

Regards,

Lin

[Anmolgan]

@v-lili6-msft  am sorry, but I forgot to tell you that I am using Posting date for matching and not base date, but I think both of my dates are in correct format?

[v-lili6-msft]

HI @Anmolgan 

If so, this should work well.

and in your report, you have filter the [Base Date.Base Date Level 01] slicer is '04.08.2012' and [Fiscal year.Fiscal year Level 01] is 'V3/2012', the data is '12/30/2016' it will return the data that from '1/1/2015' to '12/30/2016', these three conditions conflict.

and if I remove the filter

Opening Bal = CALCULATE(SUM('Monthly Collection Report PBI'[Debit credit value]), DATESBETWEEN(Data[Data], SAMEPERIODLASTYEAR(Data[Data]), LASTDATE(Data[Data])))

Regards,

Lin

[Anmolgan]

Thanks for the response, I have managed to build this, but the DAX function you have suggested is not validating correctly for the values coming by using the DAX function, Sameperiodlastyear will give me a current date sales right?

I want sales happend for this date lets say 31/12/2019 plus last year sales upto the date (31/12/2019)

I aplogize if I have not explained this correctly, I think I need to use previous year dax, any examples how can get the above?