Forum Discussion

chestercas's avatar
chestercas
Icon for Helper I rankHelper I
3 years ago

PBI - Total previous month

Hi,

I need help for a simple DAX formul.
I need to calculate a total for the previous month ( for 1 to 31). If the actual date is the 3rd November, I need the total of my column fort 1 to 31 October.
I've tested PREVIOUSMONTH  (pondération is my column with my numbers // Date d'assignation is my column with my dates) :

 

Entrant M-1 = CALCULATE(SUM('Rapport 1'[Pondération]),PREVIOUSMONTH('Rapport 1'[Date d'assignation de la commande]))
 
But this DAX don't work. The result is false (I verify on my excel sheet).
I've tested DATESINPERIOD, DATESMTD ... But nothing works correctly...
For information, i need to do this mesure to create a visual table with my result for : This month, month -1, average 3 last month
And I success to have the right result with a graph and some filters (no DAX mesure)... I don't know why nothing works with my mesure...
HELP please 🙂
(I hope you'r understand my problem...)
Thank you !
Need Help

3 Replies

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

    chestercas , You should always use date table for that

    example

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
    last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
    last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

     

    Check

    Why previousmonth does not give result when datesmtd is giving it: https://youtu.be/1KkoJehRVeg

     

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

    Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
    https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
    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 :radacad sqlbi My Video Series Appreciate your Kudos.

    • chestercas's avatar
      chestercas
      Icon for Helper I rankHelper I
      3 years ago

      Thank you, I'll check those videos. I test all of example et the result is wrong.
      Hope to find my answer in video ! Thx ! 🙂

    • chestercas's avatar
      chestercas
      Icon for Helper I rankHelper I
      3 years ago

      Hi, it doesn't work. I follow those video and i don't know why it doesn't work.
      I create a date table, like the video. I made multiple columns with few format (for test).
      And then I try DAX with my date table : PREVIOUS MONTH = no result (blank)    with  DATESMTD the result is false :'( ...
      The relation with my new date table and the date on my origin table is OK. I don't understand... 😞 But i think it's simple and it's a small thing...