Bring Value from table based on Date and additional filters

[Anonymous]

Hi everyone, I am relatively new to powerBi so I appreciate your help with the following:

I have the following data table:

Date             / DigitalFlag / VisitedFlag    / # _ operators
12/2020 / DIGITAL    / NON VISITED / 41
01/2021 / DIGITAL   / NON VISITED / 5
02/2021 / DIGITAL   / NON VISITED / 10

I would lilke to add a card showing the number of operators of the last moth for the different combinations: DIGITAL / VISITED; NON DIGITAL / VISITED; DIGITAL / NON VISITED; NON DIGITAL / NON VISITED.... 

In other words, if today is Febrary 2021, I would like a measure that brings me 5 as the number of operators for the combination DIGITAL / NON VISITED during January.

Thank you in advance for your help

[Syndicate_Admin]

Hello, please can you help me.

I have in the visual the amount of the month that is fine, the previous month that is also correct, the amount of the present year (2022) to the date of the slider range (January to May), but I do not get the amount of the previous year (2021 from January to May), in the column "Acum. YTD"

Acum. YTD = calculate(sum(ERI[Valor]),PREVIOUSYEAR('Calendar'[Fecha]))

Thanks in advance.

[parry2k]

@Anonymous 'm still not sure what is working and what is not working and what you want. It will be much easier if you share a sample pbix file with clearly defined what you are expecting and how you are planning to interact with the report. 

[parry2k]

@Anonymous there are two ways to solve it, create one measure using PREVIOUSMONTH and in visual, use Date/Digital Flag/Visited Flag and the PREVIOUSMONTH measure

or create four different measures for each combination and to do so, you need to filter Digital Flag and Visited Flag in each measure. Hope this helps.

[selimovd]

Hey @Anonymous ,

I'm not sure if you need the filter for DigitalFlag and VisitedFlag as the values are the same. But I included them.

Try the following approach:

Operators last Month =
CALCULATE(
    SUM( myTable[# _ operators] ),
    DATEADD(
        myDateTable[Date],
        -1,
        MONTH
    ),
    ALL( myDateTable ),
    myTable[DigitalFlag] = "DIGITAL" & myTable[VisitedFlag¨] = "NON VISITED"
)

Be aware that you need a proper date table that the time intelligence functions are working.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Thank you, this solution took me much closer to what I am looking for. Altough it is showing me the SUM of all operator in different months under the DIGITAL & VISITED FLAG, and I only want last month. I dont know if the fact that iI only have one value per month might be affecting ... This is how my data actually looks like:

so if I choose non digital & visited I should get from the measure: 12 which is the value from last month April. If I select digital & visited I should get 4... i dont mind creatin as may measures and add specific filters. Thank you again!
I dont mind to create different measures for each one, but for now is just addin

If I choose 

[parry2k]

@Anonymous you need to look into the time intelligence DAX functions called PREVIOUSMONTH or DATEADD

But before you use these functions you need to add a date dimension in your model which is a recommended best practice when working with time intelligence functions. Once it is added to the model, the rest is pretty straightforward. You can add date dimension following my blog post Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Thank you for answering. I already have a dim Date table in my dasboard linked to the date column of my table... the strugle is in creating the measure considering last month number of operatros for each of the flags I currently have (if visited or not ANd if digital or not).