Forum Discussion

lordsriram's avatar
lordsriram
Frequent Visitor
7 years ago

Sales Comparison over different date ranges

I currently use Previous year calculation using DAX function on date ranges based on the selected date range of invoice date on the slicer. an example below is   PY SHIPPED QUANTITY ECOM = CALCULA...
Need Help
Iamnvt's avatar
Iamnvt
Icon for Continued Contributor rankContinued Contributor
7 years ago
Hi,

You need to have 2 different calendars: 1 for current selection, 1 for previous selection.
Then you can create 2 different measures based on the selection of the calendars to compare.
lordsriram's avatar
lordsriram
Frequent Visitor
7 years ago

I tried two calendars and it didnt work, Please explain your implementation process.

 

When i add two calendars and attach them to the invoice date in the data model, the slicer will not show any data when there is two different date ranges. I need more clarity on how you will implement two calendars with slicer and data modal.

 

Thanks

  • Iamnvt's avatar
    Iamnvt
    Icon for Continued Contributor rankContinued Contributor
    7 years ago

    hi,

     

    Current calendar has the relationship

    Previous selection calendar has no relationship. It is a disconnected table.

     

    you write the 2nd measure to calculate the previous sales, based on the selected values in the 2nd calendar.

    • lordsriram's avatar
      lordsriram
      Frequent Visitor
      7 years ago

      I added the filter and the unconnected date table column. I used the following formula 

       

      PY SHIPPED AMOUNT NET ECOM = CALCULATE(SUM('PWRBI_SALES'[SHIPPED AMOUNT NET]), DATESBETWEEN('INVOICE DATE'[INVOICE DATE].[Date],FIRSTDATE('PREVIOUS DATE'[Date].[Date]),LASTDATE('PREVIOUS DATE'[Date].[Date])))
       
      The first date and last date are not returning the slicer values. I added a table to show the previous date column and that shows filter effect. The first date and last date are pretty much taking the tables first date of say 1/1/2011 and last date of 12/31/2040. If I put a date values in the first date and last date this formula works. how do i get the slicer date values into this formula
       
      Let me know