Forum Discussion

Suhel_Ansari's avatar
Suhel_Ansari
Icon for Helper V rankHelper V
6 years ago
Solved

Previuos Rows Value

Hi DAX Expert, I am struck in a very interesting issue with my DAX measure issue, I have created measures Count of rows which is Count, M_Reg and M_Ter, Running = M_Reg - M_Ter and Last Year, now i ...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Do get that first create a year table, as you have take all filter, so that will cause an issue for other filters

Assume year table is

year = dictinct(table[year])//from current table
Last year =

CALCULATE(SUM(Sales[Sales Amount]),filter(all(year),year[year]=max(year[year])-1))

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

  • Suhel_Ansari's avatar
    Suhel_Ansari
    Icon for Helper V rankHelper V
    6 years ago

    amitchandak 
    I am checking on that if i can get the date column. Thanks

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Suhel_Ansari 

      if you can get date column then create a time table and use formulas.

      In can, you only have year you can create date like

      date = date(year(table[year],1,1)

       

      These formula's can help. Oce you have date

      YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

       

      To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

      Refer
      https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
      https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

      https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/